Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


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

Home » Article Discussions » Article Discussions by Author » Discuss content posted by sushant.approva » SQL LEN Function
Add to briefcase
18 posts, Page 1 of 2
12»»

SQL LEN Function Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Site Owners
Posted Wednesday, September 09, 2009 11:49 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

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

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 12:49 PM
Points: 785, Visits: 1,536
ouch. didn't know about the trailing space removal before length calculation. nice question!
Post #785661
Doron Hadar
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: Wednesday, June 12, 2013 1:44 AM
Points: 441, Visits: 630
ouch indeed...
Good one.
Post #785682
Rick Romack
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: Tuesday, April 16, 2013 4:22 PM
Points: 1,032, Visits: 284
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
david.wright-948385
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: Tuesday, May 21, 2013 4:41 AM
Points: 909, Visits: 596
try SELECT '''' + @str + '''' :)
Post #785711
Gatekeeper
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: Wednesday, May 15, 2013 6:29 PM
Points: 387, Visits: 681
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
Michael Poppers
Posted Thursday, September 10, 2009 8:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 12:35 PM
Points: 572, Visits: 366
kramaswamy (9/10/2009)
ouch. didn't know about the trailing space removal before length calculation. nice question!

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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:08 PM
Points: 2,121, Visits: 2,226
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
SanjayAttray
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: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
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
Cliff Jones
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: Thursday, June 13, 2013 11:45 AM
Points: 3,417, Visits: 3,435
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
18 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.