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 9, 2009 11:49 PM
 SSChampion Group: General Forum Members Last Login: Friday, May 18, 2007 3:36 PM Points: 10,041, 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! Group: General Forum Members Last Login: Yesterday @ 4:16 PM Points: 850, Visits: 1,786
 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: Wednesday, September 2, 2015 9:48 AM Points: 478, Visits: 675
 ouch indeed...Good one.
Post #785682
 Posted Thursday, September 10, 2009 8:07 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, August 13, 2015 9:57 AM Points: 1,056, Visits: 297
 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
 Ten Centuries Group: General Forum Members Last Login: Wednesday, November 23, 2016 1:21 AM Points: 1,030, Visits: 955
 try SELECT '''' + @str + '''' :)
Post #785711
 Posted Thursday, September 10, 2009 8:13 AM
 Old Hand Group: General Forum Members Last Login: Thursday, April 21, 2016 4:37 AM Points: 392, Visits: 888
 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
 Right there with Babe Group: General Forum Members Last Login: Saturday, December 3, 2016 9:23 PM Points: 746, Visits: 416
 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: Today @ 8:56 AM Points: 2,945, Visits: 3,705
 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 -------------------"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script"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 Group: General Forum Members Last Login: Wednesday, January 6, 2016 1:49 PM Points: 3,925, Visits: 1,619
 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: Monday, November 10, 2014 12:05 PM Points: 3,969, Visits: 3,648
 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