Example
Returns the number of characters of a string.
Note: the LEN function ignores trailing spaces:
SELECT LEN('My string'), -- returns 9
LEN('My string '), -- returns 9
LEN(' My string') -- returns 12
If the length including trailing spaces is desired there are several techniques to achieve this, although each has its drawbacks. One technique is to append a single character to the string, and then use the LEN minus one:
DECLARE @str varchar(100) = 'My string '
SELECT LEN(@str + 'x') - 1 -- returns 12
The drawback to this is if the type of the string variable or column is of the maximum length, the append of the extra character is discarded, and the resulting length will still not count trailing spaces. To address that, the following modified version solves the problem, and gives the correct results in all cases at the expense of a small amount of additional execution time, and because of this (correct results, including with surrogate pairs, and reasonable execution speed) appears to be the best technique to use:
SELECT LEN(CONVERT(NVARCHAR(MAX), @str) + 'x') - 1
Another technique is to use theDATALENGTH function.
DECLARE @str varchar(100) = 'My string '
SELECT DATALENGTH(@str) -- returns 12
It's important to note though that DATALENGTH returns the length in bytes of the string in memory. This will be different for varchar vs. nvarchar.
DECLARE @str nvarchar(100) = 'My string '
SELECT DATALENGTH(@str) -- returns 24
You can adjust for this by dividing the datalength of the string by the datalength of a single character (which must be of the same type). The example below does this, and also handles the case where the target string happens to be empty, thus avoiding a divide by zero.
DECLARE @str nvarchar(100) = 'My string '
SELECT DATALENGTH(@str) / DATALENGTH(LEFT(LEFT(@str, 1) + 'x', 1)) -- returns 12
Even this, though, has a problem in SQL Server 2012 and above. It will produce incorrect results when the string contains surrogate pairs (some characters can occupy more bytes than other characters in the same string).
Another technique is to use REPLACE to convert spaces to a non-space character, and take the LEN of the result. This gives correct results in all cases, but has very poor execution speed with long strings.
