A Simple Question about the Len function

Question

Post reply

A Simple Question about the Len function

meichner

SSCertifiable

Points: 5563
More actions
July 17, 2008 at 7:40 am

#210163

I was wondering why the Len function doesn't count trailing spaces as part of the length of the string? I am sure there is a good reason, I am just curious as to what that reason is?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

I am not sure exaclty why, but here is a workaround. Just concatenate a character and subtract one.

Declare @a char(6)

Set @a = 'a '

select len(@a)

select len(@a + '1') -1

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

I'm not sure this is a definitive answer, but I would venture to guess that it does not include trailing spaces because in fixed length character columns SQL Server pads the right side so if you want to know the actual length of a value in that column you would have to trim and then use len. There a function, DataLength, that does include trailing spaces. Try this out:

[font="Courier New"]USE AdventureWorks;

GO

SELECT

DATALENGTH(Name) AS includes_spaces,

LEN(Name) AS trims_spaces,

Name

FROM

Production.Product

ORDER BY

Name;

GO[/font]

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

thegreatsamarth SSChasing Mays Points: 608 More actions · Answer 3

Hi...

As per my thinking, by default Trailing Spaces are not considered in strings.

So there are many ways to handle out tht... as like...

Examples below will return correct Length including Trailing spaces:

----------------------------------------------------------------------

DECLARE @STR VARCHAR(50)

SET @STR = 'abc '

--As QUOTENAME() will add '[' and ']' in ur string, substract 2 from lenght

SELECT LEN(QUOTENAME(@str))-2

SELECT LEN(REVERSE(@str))

----------------------------------------------------------------------

Samarth

meichner SSCertifiable Points: 5563 More actions · Answer 4

Actually SSCrazy's response seems to make sense. I had not thought about fixed length strings.

Thanks

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

As for why it excludes the trailing spaces, who knows? It is working as documented in SQL Server Books Online:

"Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks."

You can use the datalength function to get the length in bytes, including trailing spaces. You should be aware that for unicode datatypes (nchar, nvarchar, ntext), that there are two bytes for each character. See example below with varchar and nvarchar datatypes for each function.

select

x,

[len_x] = len(x),

[datalength_x] = datalength(x),

[len_z] = len(z),

[datalength_z] = datalength(z)

from

(

-- Test data

select x = ' ', z = N' 'union all

select x = 'y ', z = N'y '

) a

Results:

x len_x datalength_x len_z datalength_z

---- ----------- ------------ ----------- ------------

0 2 0 4

y 1 2 1 4

(2 row(s) affected)

GSquared SSC Guru Points: 260824 More actions · Answer 6

If you want to know why it works that way, take a look at the article on ANSI_Padding in Books Online. Tells you exactly why and how it works.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jack Corbett SSC Guru Points: 184393 More actions · Answer 7

MVJ is correct in that DataLength returns to storage bytes so unicode will return 2 bytes per character. So you do need to know the data type in order to understand the data returned.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question