Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 SQL LEN Function Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, September 09, 2009 11:49 PM
 SSChampion 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
 Right there with Babe Group: General Forum Members Last Login: Thursday, December 05, 2013 2:17 PM Points: 791, Visits: 1,577
 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 Group: General Forum Members Last Login: Thursday, December 05, 2013 4:18 AM Points: 464, Visits: 653
 ouch indeed...Good one.
Post #785682
 Posted Thursday, September 10, 2009 8:07 AM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, April 16, 2013 4:22 PM Points: 1,032, Visits: 284
 I do disagree with this answer,If I runDECLARE @str NVARCHAR(10)SET @str = '1, 2, 3, 'SET @str = LEFT(@str, LEN(@str)- 2)select len(@str)SELECT @strThe 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! Group: General Forum Members Last Login: Thursday, November 21, 2013 2:05 PM Points: 928, Visits: 675
 try SELECT '''' + @str + '''' :)
Post #785711
 Posted Thursday, September 10, 2009 8:13 AM
 Old Hand Group: General Forum Members Last Login: Yesterday @ 6:59 PM Points: 393, Visits: 744
 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 @strSELECT RIGHT(@str,1) -- returns space, not a commaBTW, 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 Group: General Forum Members Last Login: Sunday, November 24, 2013 10:33 AM Points: 607, Visits: 395
 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 Group: General Forum Members Last Login: Yesterday @ 9:34 AM Points: 2,218, Visits: 2,411
 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 -------------------"The chemistry must be respected." - Walter White"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 Group: General Forum Members Last Login: Friday, December 06, 2013 11:21 AM Points: 3,924, Visits: 1,572
 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 Group: General Forum Members Last Login: Yesterday @ 7:57 AM Points: 3,608, Visits: 3,534
 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

 Permissions