SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL LEN Function


SQL LEN Function

Author
Message
Manie Verster
Manie Verster
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2293 Visits: 1030
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
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 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
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 341
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-Addicted
SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)SSC-Addicted (443 reputation)

Group: General Forum Members
Points: 443 Visits: 612
Also, LEN counts trailing tabs, but not spaces.
Adrian Charles Chetwynd-Talbot...
Adrian Charles Chetwynd-Talbot
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 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
SSChasing Mays
SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)SSChasing Mays (615 reputation)

Group: General Forum Members
Points: 615 Visits: 341
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
SSC Eights!
SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)SSC Eights! (801 reputation)

Group: General Forum Members
Points: 801 Visits: 237
didn't see option with space there...
Duncan Pryde
Duncan Pryde
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4174 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