Skip to content

LEN() function returning wrong number!

by Dan Thompson on May 13th, 2013

LEN() Function has caught me out again!

LEN() doesn’t take into consideration trailing spaces. I guess this must be to do with the varchar type, but actually it applies to any string you manually enter in. Take the following code as an example

SELECT LEN('5     ')

There are 5 spaces after the number 5, that’s 6 characters in total. The result returned from this query is 1 is LEN() trims the trailing spaces.

In order to count the full length of the string in this case, we must use the DATALENGTH() function.

So – Why do so many people use the LEN() function automatically? …well DATALENGTH is just for the amount of bytes an expression takes up, Length is the number of characters. But then, DATALENGTH for a string seems to return length of characters!

Clear? As mud!

More Information on MSDN:
DATALENGTH (Transact-SQL)
LEN (Transact-SQL)

From → T-SQL

  • Fux Molder

    I’m having problems with length. 144000835147653 is counted as 9 long every time. Anybody know why ?

    • Dan Thompson

      Hi Fux,
      Can you clarify what you mean by it is being counted as 9 long every time?

      • Fux Molder

        Result of datalength(144000835147653) is 9.
        PS. I’m impressed with the speed of your reply mate :D