Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL LEN Function Expand / Collapse
Author
Message
Posted Wednesday, September 9, 2009 11:49 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item SQL LEN Function
Post #785512
Posted Thursday, September 10, 2009 7:04 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
ouch. didn't know about the trailing space removal before length calculation. nice question!
Post #785661
Posted Thursday, September 10, 2009 7:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:02 AM
Points: 466, Visits: 666
ouch indeed...
Good one.
Post #785682
Posted Thursday, September 10, 2009 8:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:38 AM
Points: 1,036, Visits: 288
I do disagree with this answer,
If I run
DECLARE @str NVARCHAR(10)
SET @str = '1, 2, 3, '
SET @str = LEFT(@str, LEN(@str)- 2)
select len(@str)
SELECT @str
The return for len(@str) returns 5 that would be '1, 2,' with no space after the comma.

If I copy the result of the SELECT @str to word and have it show all characters there is no space after the comma.
Post #785700
Posted Thursday, September 10, 2009 8:12 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, September 13, 2014 4:06 AM
Points: 988, Visits: 804
try SELECT '''' + @str + '''' :)
Post #785711
Posted Thursday, September 10, 2009 8:13 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:53 AM
Points: 393, Visits: 812
Rick, it does return the space at the end:

DECLARE @str NVARCHAR(10)
SET @str = '1, 2, 3, '
SET @str = LEFT(@str, LEN(@str)- 2)
select len(@str)
SELECT @str
SELECT RIGHT(@str,1) -- returns space, not a comma

BTW, I got it wrong too.


/* Anything is possible but is it worth it? */
Post #785712
Posted Thursday, September 10, 2009 8:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:58 AM
Points: 654, Visits: 400
kramaswamy (9/10/2009)
ouch. didn't know about the trailing space removal before length calculation. nice question!

Ditto .
Post #785728
Posted Thursday, September 10, 2009 8:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 2,369, Visits: 2,719
Michael Poppers (9/10/2009)
kramaswamy (9/10/2009)
ouch. didn't know about the trailing space removal before length calculation. nice question!

Ditto .


Ouch is right. I got the fact that the LEN ignores the last space, but mistakenly thought that it trims the resulting string. But now I see that it doesn't. It returns the exact remaining string, even if the last character in the trimmed string is itself a space.

Nice question. Tricky in a way but not simply "gotcha" trivia. It's the kind of trickiness one needs to know when dealing with strings that have spaces.

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #785739
Posted Thursday, September 10, 2009 9:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
If executed with result in text then you see space after , . But, if result set in grid it does not show space with copy and paste result set in notepad.

SQL DBA.
Post #785758
Posted Thursday, September 10, 2009 9:50 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:36 AM
Points: 3,918, Visits: 3,638
Yes, interesting question. Can be fooled by the fact that ...

SELECT len (@str),@str

Returns:
----------- ----------
5 1, 2,

...with a space after the last comma.
Post #785811
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse