Wednesday, March 16, 2011 8:49 PM
Watch out when using T-SQL Len()
To quote the online help: “…excluding trailing blanks”.
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.
select len(‘ ’) –- returns 0
select len(‘ X’) –- returns 2
select len(‘ X ’) –- returns 2
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!
Filed under: T-SQL
select DETALENGTH(‘ X’) –- returns 2
select DATALENGTH(‘ X ’) –- returns 3!