In SQL Server (and Azure), the T-SQL LEN() function returns the number of characters of a specified string expression, excluding trailing blanks.

You provide the string as an argument.

Syntax

The syntax goes like this:

LEN ( string_expression )

Where

string_expression

is the string for which you’d like the length returned.

Example

Here’s an example:

SELECT LEN('Lit');

Result:

3

Trailing Blanks

The LEN() function doesn’t count trailing blanks (e.g. spaces at the end, tabs, carriage returns, etc). Therefore, the following example also returns 3:

SELECT LEN('Lit ');

Result:

3

This is in contrast to the DATALENGTH() function, which does count trailing blanks (and therefore would return 4 in this example).

Leading Blanks

However, the LEN() function does count leading blanks. Therefore, the following example returns 4:

SELECT LEN(' Lit');

Result:

4

This is the same as the DATALENGTH() function, which also counts leading blanks.

Return Type

The LEN() function returns a data type of int, unless the expression is  varchar(max), nvarchar(max) or varbinary(max), in which case it returns bigint.