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 Friday, September 11, 2009 6:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 12:18 AM
Points: 1,205, Visits: 924
Rick Romack (9/10/2009)
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.


I was about to say I agree with you Rick and I actually did until......
DECLARE @str NVARCHAR(10)
SET @str = '1, 2, 3, '
SET @str = LEFT(@str, LEN(@str)- 2)
select '"'+@str+'"' mystr, len(@str) lenstr

The result as copied from sql server 2005 and 2008. Both gave the same results.
mystr lenstr
"1, 2, " 5

When I pasted the result in here directly from ssms I saw that there was actually a space at the end but what stumps me is the fact that it gave a LEN of 5 characters and that does not include the space at the end. Can someone please clear this for me 'cause I am stumped.


Manie Verster
Developer
Johannesburg
South Africa

I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
Post #786290
Posted Friday, September 11, 2009 7:53 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 392, Visits: 820
Manie Verster (9/11/2009)

When I pasted the result in here directly from ssms I saw that there was actually a space at the end but what stumps me is the fact that it gave a LEN of 5 characters and that does not include the space at the end. Can someone please clear this for me 'cause I am stumped.


http://msdn.microsoft.com/en-us/library/ms190329.aspx

It's just the way MS decided to display length. It could be part of an ISO standard on how to report string lengths. If you wanted true length, do LEN(@str+'-')-1.


/* Anything is possible but is it worth it? */
Post #786375
Posted Friday, September 11, 2009 9:08 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:14 AM
Points: 487, Visits: 261
very interesting. I too thought the results would be "1, 2,". Until I set the output to text and realized I was wrong.

I thought the final select would "automatically" trim the trailing space since the field type was set to varchar. Again very interesting....
Post #786461
Posted Friday, September 11, 2009 9:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 2:44 PM
Points: 121, Visits: 373
Also, LEN counts trailing tabs, but not spaces.
Post #786516
Posted Thursday, September 17, 2009 5:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 10, 2010 5:12 AM
Points: 36, Visits: 31
the given answer to this question is incorrect because the string variable contains 9 characters including the commas and spaces. Total length through first function being 9 - 2 = 7 and the first seven characters from the left is 1, 2, 3 which received more percentage of answers from the membership.
Post #789554
Posted Thursday, September 17, 2009 7:01 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Friday, November 14, 2014 7:14 AM
Points: 487, Visits: 261
Adrian Charles Chetwynd-Talbot (9/17/2009)
the given answer to this question is incorrect because the string variable contains 9 characters including the commas and spaces. Total length through first function being 9 - 2 = 7 and the first seven characters from the left is 1, 2, 3 which received more percentage of answers from the membership.


Keep in mind the data type nvarchar will remove the trailing space. So the length only starts with 8 characters rather then 9. The trick in this question was the trailing space was not removed after the second SET.
Post #789598
Posted Monday, October 4, 2010 7:19 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
didn't see option with space there...
Post #997533
Posted Tuesday, February 22, 2011 11:37 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, October 21, 2014 6:56 AM
Points: 3,352, Visits: 1,487
Nice question - had the same "doh!" moment as lots of other people by the look of it!
Post #1067788
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse