SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Len() function in Select Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 08/01/2001 :  00:09:44  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Maya writes "My select statement returns a resultset with one field, 'ResultText'. I would like to get a length of that field. Is there a way in SQL to do it like in Visual Basic ?"

Article Link.

mfemenel
Professor Frink

USA
1421 Posts

Posted - 08/01/2001 :  08:33:32  Show Profile  Visit mfemenel's Homepage  Reply with Quote
To avoid the null return value, you can wrap the len function in an isnull function

isnull(len(notes),0) so you'll get 0 if there is a null value.

Just a personal quirk,not a big fan of null values.

Mike F
Go to Top of Page

davidpardoe
Constraint Violating Yak Guru

United Kingdom
324 Posts

Posted - 08/01/2001 :  10:17:51  Show Profile  Reply with Quote
I believe Nulls are important if the piece of data is truly unknown.

I have come across too many cases where numeric fields are set to 0 if unknown, resulting in incorrect usage - eg averages, where criteria involving field<value etc.

I do admit that holding null values does mean being careful when coding but I would rather that than users using data incorrectly - woe betide

Go to Top of Page

billsox
Yak Posting Veteran

74 Posts

Posted - 08/01/2001 :  16:16:25  Show Profile  Reply with Quote
quote:

Maya writes "My select statement returns a resultset with one field, 'ResultText'. I would like to get a length of that field. Is there a way in SQL to do it like in Visual Basic ?"<P>Article <a href="/item.asp?ItemID=5054">Link</a>.



This article has some incorrect information. The LEN function does NOT work exactly like it does in Visual Basic. In VB the following statement would return a value of 6 :

MsgBox Len("joe ")

In SQL (all versions), the LEN function returns the length of the *right-trimmed* string. So the following statement returns a value of 3.

SELECT LEN('joe ')

To get the exact length, use the DATALENGTH function.


Go to Top of Page

Doug G
Constraint Violating Yak Guru

USA
330 Posts

Posted - 08/02/2001 :  01:20:25  Show Profile  Reply with Quote
quote:

This article has some incorrect information. The LEN function does NOT work exactly like it does in Visual Basic. In VB the following statement would return a value of 6 :

MsgBox Len("joe ")

Well, your VB must count slightly different than mine, I get 3 as I would expect.


======
Doug G
======
Go to Top of Page

empire
Starting Member

40 Posts

Posted - 08/02/2001 :  02:09:07  Show Profile  Reply with Quote
Weird for VB:
note the space after the word joe .
len("joe ") will return 4.

SQL:
(same space after the word joe)
select len('joe ') will return 3
select datalength( 'joe ') will return 4




Go to Top of Page

billsox
Yak Posting Veteran

74 Posts

Posted - 08/02/2001 :  10:03:02  Show Profile  Reply with Quote
quote:

quote:

This article has some incorrect information. The LEN function does NOT work exactly like it does in Visual Basic. In VB the following statement would return a value of 6 :

MsgBox Len("joe ")

Well, your VB must count slightly different than mine, I get 3 as I would expect.


======
Doug G
======



When I typed in my original post, I put in 3 addtional spaces after the word "joe" but when it is presented via HTML, multiple spaces are trimmed to one space. My fault!

Go to Top of Page

Doug G
Constraint Violating Yak Guru

USA
330 Posts

Posted - 08/02/2001 :  18:33:02  Show Profile  Reply with Quote
quote:
When I typed in my original post, I put in 3 addtional spaces after the word "joe" but when it is presented via HTML, multiple spaces are trimmed to one space. My fault!

Nah, I didn't even notice the single space. I wasn't aware of the difference between VB and SQL LEN either, thanks for pointing it out.


======
Doug G
======
Go to Top of Page

dewee
Starting Member

United Kingdom
2 Posts

Posted - 09/12/2001 :  13:34:13  Show Profile  Reply with Quote
quote:

Maya writes "My select statement returns a resultset with one field, 'ResultText'. I would like to get a length of that field. Is there a way in SQL to do it like in Visual Basic ?"<P>Article <a href="/item.asp?ItemID=5054">Link</a>.

Go to Top of Page

dewee
Starting Member

United Kingdom
2 Posts

Posted - 09/12/2001 :  13:37:42  Show Profile  Reply with Quote
On a related note - if you want to return the length of a numeric field you need the following:

SELECT LEN(CONVERT(VARCHAR, intColumnName)) FROM tblName

This can be useful when combined with the REPLICATE function for padding numeric fields. E.g. 13 to '000013'
Go to Top of Page

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 09/12/2001 :  13:51:04  Show Profile  Visit robvolk's Homepage  Reply with Quote
There's a shorter method for padding numbers with zeros:

SELECT REPLACE(STR(numberCol, 10, 0), ' ', '0') FROM ...

The STR() function will format a number to a fixed number of digits and decimal places, padding the left with spaces. The REPLACE() function will then change the spaces to zero (or whatever character you want).

Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 10/27/2001 :  19:33:21  Show Profile  Visit nr's Homepage  Reply with Quote
What about unicode strings?

for padding I use

SELECT right(replicate('0',5) + CONVERT(VARCHAR(5), intColumnName)),5) FROM tblName



==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Parax
Starting Member

3 Posts

Posted - 05/30/2003 :  11:13:04  Show Profile  Reply with Quote
Use {Code}Code here{/Code} tags to avoid forum errors!! (subst {} with [])

LEN('Joe   ')



For Padding I just use:
SELECT Format(intColumnName,'000000') FROM tblName
seems shorter still ;)

Go to Top of Page

cas_o
Posting Yak Master

United Kingdom
154 Posts

Posted - 05/30/2003 :  12:49:37  Show Profile  Visit cas_o's Homepage  Reply with Quote
WHAT?

format is a clause for database backups not a function, I can't make that work in query analyser, I thoght it looked to good to be true.

;-]
Go to Top of Page

killerguru
Starting Member

1 Posts

Posted - 04/20/2004 :  05:38:39  Show Profile  Visit killerguru's Homepage  Reply with Quote
select right(CONVERT(VARCHAR, (5+1000000000)),3)

A REALLY SIMPLE WAY TO PAD.

TRY IT OUT

:-))
Go to Top of Page

greg85374
Starting Member

USA
8 Posts

Posted - 07/16/2004 :  15:34:23  Show Profile  Send greg85374 a Yahoo! Message  Reply with Quote
Most often I see UNKNOWN or something similar in place of a null value. then in your code you ignore the UNKNOWN. There is no real reason I can think of you would EVER want to have a null value!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000