SQL Server LEN() Function

Summary: in this tutorial, you will learn how to use the SQL Server LEN() function to return the number of characters of an input string.

SQL Server LEN() function overview

The LEN() function returns the number of characters of an input string, excluding the trailing blanks.

Here’s the syntax of the LEN() function:

LEN(string_expression)Code language: SQL (Structured Query Language) (sql)

In this syntax, the string_expression can be a literal character string, string expression, or a character or binary column.

The LEN() function returns a value whose data type is BIGINT if string_expression is of the VARCHAR(max), NVARCHAR(max) or VARBINARY(max) data type; or INT otherwise.

SQL Server LEN() function examples

Let’s take some examples of using the LEN() function.

1) Using LEN() function with a literal string

The following example uses the LEN function to return the number of characters of the string SQL Server LEN and the same string with trailing blanks.

SELECT 
  LEN('SQL Server LEN') length, 
  LEN('SQL Server LEN   ') length_with_trailing_blanks;Code language: SQL (Structured Query Language) (sql)

Here is the output:

length      length_with_trailing_blanks
----------- ---------------------------
14          14

The output indicates that the LEN() function does not count the trailing blanks.

If you want to consider the trailing spaces, you can use the DATALENGTH() function. For example:

SELECT 
  DATALENGTH('SQL Server LEN') data_length, 
  DATALENGTH('SQL Server LEN   ') data_length_with_trailing_blanks;Code language: JavaScript (javascript)

Output:

data_length data_length_with_trailing_blanks
----------- --------------------------------
14          17

2) Using the LEN() function with a data

We’ll use the production.products table from the sample database.

The following query retrieves the product names and their length using the LEN() function:

SELECT 
  product_name, 
  LEN(product_name) product_name_length 
FROM 
  production.products 
ORDER BY 
  product_name_length DESC;Code language: SQL (Structured Query Language) (sql)

The following picture shows the partial output:

3) Using the LEN() function with NULL

The following example uses the LEN() function with NULL, which results in NULL:

SELECT 
  LEN(NULL) result;Code language: PHP (php)

Output:

result
-----------
NULLCode language: PHP (php)

Summary

• Use the LEN() function to return the number of characters of a string, excluding trailing spaces.