| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
|
|
mfemenel
Professor Frink
USA
1414 Posts |
 Posted - 08/01/2001 : 08:33:32
|
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
|
 |
|
|
davidpardoe
Constraint Violating Yak Guru
United Kingdom
324 Posts |
Posted - 08/01/2001 : 10:17:51
|
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 
|
|
|
|
billsox
Yak Posting Veteran
74 Posts |
Posted - 08/01/2001 : 16:16:25
|
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.
|
|
|
|
Doug G
Constraint Violating Yak Guru
USA
324 Posts |
Posted - 08/02/2001 : 01:20:25
|
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
====== |
|
|
|
empire
Starting Member
40 Posts |
Posted - 08/02/2001 : 02:09:07
|
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
|
|
|
|
billsox
Yak Posting Veteran
74 Posts |
Posted - 08/02/2001 : 10:03:02
|
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!
|
|
|
|
Doug G
Constraint Violating Yak Guru
USA
324 Posts |
Posted - 08/02/2001 : 18:33:02
|
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
====== |
|
|
|
dewee
Starting Member
United Kingdom
2 Posts |
Posted - 09/12/2001 : 13:34:13
|
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>.
|
|
|
|
dewee
Starting Member
United Kingdom
2 Posts |
Posted - 09/12/2001 : 13:37:42
|
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' |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 09/12/2001 : 13:51:04
|
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).
|
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 10/27/2001 : 19:33:21
|
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. |
|
|
|
Parax
Starting Member
3 Posts |
Posted - 05/30/2003 : 11:13:04
|
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 ;)
|
|
|
|
cas_o
Posting Yak Master
United Kingdom
154 Posts |
Posted - 05/30/2003 : 12:49:37
|
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.
;-] |
|
|
|
killerguru
Starting Member
1 Posts |
Posted - 04/20/2004 : 05:38:39
|
select right(CONVERT(VARCHAR, (5+1000000000)),3)
A REALLY SIMPLE WAY TO PAD.
TRY IT OUT
:-)) |
|
|
|
greg85374
Starting Member
USA
8 Posts |
Posted - 07/16/2004 : 15:34:23
|
| 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! |
|
|
| |
Topic |
|
Article: Len() function in Select Statement
