April 12, 2021

The SQL LEN function returns the number of characters within a string.  Keep in mind the count of characters returned does not include training spaces.

Description

The SQL LEN function return the length of a string (number of characters).  It does not include trailing spaces.

The LEN function returns the number of characters in a string. In the following example you can see that the length of “SQL Server” is 10.

Visual Explanation of the LEN function.
Example of the LEN function

Length is rarely used on its own.  It is used mainly in conjunction with other functions, such as LEFT and RIGHT.

In this example, we’ll use the HumanResource.Employee table for our examples.

Employee Example Data for SQL LEN function

Using the data above, let’s use LEN to find out the number of characters within JobTitle:

SELECT JobTitle, LEN(JobTitle) JobTitleLength FROM HumanResources.Employee
/* Answer */
SELECT JobTitle, LEN(JobTitle) JobTitleLength
FROM  HumanResources.Employee

SQL LEN Usage Notes

  • The LEN function does not count spaces at the end of the string.
  • The result of LEN(NULL)  is NULL.
  • LEN returns an integer, except when using the max data length, such as VARCHAR(MAX).  In this case it returns bigint.
  • Use DATALENGTH to return number of bytes used within string.

Syntax

LEN(string)

Where string is constant, variable, or column of either character or binary data.

SQL LEN Examples

Return the length of a string using LEN:

SELECT LEN('EssentialSQL.com') Length;
/* Answer */
SELECT LEN('EssentialSQL.com') Length;

Return the length of a string containing trailing spaces:

SELECT LEN('EssentialSQL.com ') Length;
/* Answer */
SELECT LEN('EssentialSQL.com     ')  Length;

Using LEN within a where clause. Return job titles with 15 or more characters:

SELECT DISTINCT JobTitle FROM HumanResources.Employee WHERE LEN(JobTitle) >= 15;
/* Answer */
SELECT DISTINCT JobTitle
FROM  HumanResources.Employee
WHERE LEN(JobTitle) >= 15;

What is the length of a NULL string?

SELECT LEN(NULL) Length;
/* Answer */
SELECT LEN(NULL) Length;

See Also

About the author 

Kris Wenzel

Kris Wenzel has been working with databases over the past 30 years as a developer, analyst, and DBA. He has a BSE in Computer Engineering from the University of Michigan and a MBA from the University of Notre Dame. Kris has written hundreds of blog articles and many online courses. He loves helping others learn SQL.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Are YOU READY TO START LEARNING SQL SERVER, BUT DON'T KNOW WHERE TO START?

Nothing is worse than, being excited to learn a new tool but not knowing where to start, wasting time learning the wrong features, and being overwhelmed .

But it doesn't have to be this way.

I'm Putting together a free email course to help you get started learning SQL Server.

Name*
Email*
>