T-SQL 101: 47 Determining the length of strings in SQL Server

You might need to work out how long a string is. There are two ways you could measure the length of a string. The first is the number of characters; the second is the number of bytes (i.e. the amount of data).

You can see both here:

Even though I’ve provided a Chinese string, LEN still returns 2 as the number of characters in the string.

But because they were each two-byte characters, the function DATALENGTH returns 4 as the total number of bytes.

One of the challenges with the LEN function is that it ignore trailing spaces. So the string ‘hello’ and the string ‘hello    ’ are both length 5 according to the LEN function.

If you would prefer a function that returns the actual number of characters, including trailing spaces, I’d suggest you look at the StringLength function in our free SDU Tools for developers and DBAs.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-12-09