1

Dooes the SQL LEN() function internally TRIM() the data prior to calculating length of the string?

Example:

DECLARE @a VARCHAR(MAX), @b VARCHAR(MAX)
SET @a = '12345 '
SET @b = '12345 7'

SELECT @a, LEN(@a)
-- 12345    5

SELECT @b, LEN(@b)
-- 12345 7  7

Both @a and @b have 7 characters. @a has 5 numeral characters and two spaces at the end.

When copying both the results from the Results window, I can see that both variables have a length of 7 chars. But when trying to find the length of the variables using LEN() it differs. The same thing happens while storing data in a table with a varchar column.

Nate Barbettini
  • 51,256
  • 26
  • 134
  • 147
Kihtrak J
  • 132
  • 1
  • 2
  • 7
  • If you want the length of the string including spaces at the end or at the beginning you can use `DATALENGTH` instead – DrCopyPaste Apr 24 '14 at 15:51

3 Answers3

5

It excludes the trailing blanks

According to LEN (Transact-SQL)

"Returns the number of characters of the specified string expression, excluding trailing blanks."

Also similar questions about

Why does t-sql's LEN() function ignore the trailing spaces?

LEN function not including trailing spaces in SQL Server

Community
  • 1
  • 1
huMpty duMpty
  • 14,346
  • 14
  • 60
  • 99
1

LEN excludes trailing blanks.

I would use

LEN(REPLACE(expr, ' ', '_'))

This should work with varchar and nvarchar and also with strings containing special unicode control characters. The DATALENGTH function suggested at other places will not yield the correct result in all cases.

You could also use

LEN(expr + '_') - 1

But the resulting string might exceed the maximum text length in some cases.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
0

It doesn't "trim" the source per se (meaning it doesn't alter it) - but it does exclude training blanks:

From the documentation:

Returns the number of characters of the specified string expression, excluding trailing blanks.

D Stanley
  • 149,601
  • 11
  • 178
  • 240