Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL LEN Function


SQL LEN Function

Author
Message
Manie Verster
Manie Verster
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1308 Visits: 986
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.

:-PManie 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)
Gatekeeper
Gatekeeper
SSC-Addicted
SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)SSC-Addicted (404 reputation)

Group: General Forum Members
Points: 404 Visits: 888
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? */
tmacs33
tmacs33
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 335
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....
rchantler
rchantler
SSC-Enthusiastic
SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)SSC-Enthusiastic (158 reputation)

Group: General Forum Members
Points: 158 Visits: 548
Also, LEN counts trailing tabs, but not spaces.
Adrian Charles Chetwynd-Talbot...
Adrian Charles Chetwynd-Talbot
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
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. w00t
tmacs33
tmacs33
Mr or Mrs. 500
Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)Mr or Mrs. 500 (503 reputation)

Group: General Forum Members
Points: 503 Visits: 335
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. w00t


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.
Dhruvesh Shah
Dhruvesh Shah
SSChasing Mays
SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)SSChasing Mays (635 reputation)

Group: General Forum Members
Points: 635 Visits: 237
didn't see option with space there...
Duncan Pryde
Duncan Pryde
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3396 Visits: 1552
Nice question - had the same "doh!" moment as lots of other people by the look of it!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search