Wednesday, March 16, 2011 8:49 PM Mikael Sand

Watch out when using T-SQL Len()

The problem

To quote the online help: “…excluding trailing blanks”.

The issue

I was writing some position based datastrings using SQL server and was continually using the Len function to evaluate the total length of the string. This is a useful technique when the total string length is about 660 positions.

Sometimes when I used Len I got strange results. The strings where much shorter than they where supposed to be.

This was, as I noticed, down to the len-function ignoring trailing blanks. *sigh* SQL server did that thing again that gives me a headache.

Examples

select len(‘ ’) –- returns 0
select len(‘ X’) –- returns 2
select len(‘ X ’) –- returns 2

A solution

A useful solution to this problem is given in the books online article: replace the blank, using the CHAR-function when evaluating the string length:

LEN(REPLACE(' x ', CHAR(32), '_')) -– returns 3

A better solution

A better and simpler solution was provided by commenter "iain", thanks! Simply use the DATALENGTH-function instead.

select DATALENGTH(‘ ’) –- returns 1!
select DETALENGTH(‘ X’) –- returns 2
select DATALENGTH(‘ X ’) –- returns 3!

Filed under:

Comments

# re: Watch out when using T-SQL Len()

Monday, April 04, 2011 4:25 PM by iain

select datalength(' ')

select datalength(' X')

select datalength(' X ')