SQL LEN Function

The SQL Server LEN function helps find the length of a specified string expression. Or we can say that the LEN Function counts the total number of characters in the given string expression and returns the INT or integer value as output. It counts the spaces at the beginning and middle of the text and omits the empty spaces at the end.

SQL LEN Function Syntax

The syntax of the LEN or LENGTH Function is as shown below.

SELECT LEN (String_Expression)
FROM [Source]

String_Expression: As the name suggests, it is a string expression that has to be evaluated to find the length. It can be a variable, a constant, or a table column that contains either characters, words, or binary data.

Return Value: It returns the total number of characters within a string_expression. However, the SQL LEN function excludes the trailing spaces. For the regular expressions, it returns INT as the output, but for VARCHAR(MAX), NVARCHAR(MAX), or VARBINARY(MAX) data types, it returns BIGINT.

TIP: If you pass an empty string as the argument value, it returns 0, and if you pass a NULL value, the output will become NULL.

SQL LEN Function Example

In this example, we will apply the LEN function to calculate the length of a given string. From the example query below, you can see that we used the LEN String Function directly on the string.

SELECT LEN('Tutorial Gateway') AS ln1;
ln1
-----------
16

How to find the length of a string Variable?

As we mentioned earlier, the LENGTH function also accepts a variable as an argument. In this example, the query declared a string variable with a length of 50 and set the string data. Next, we used this SQL Server LEN function to find the length of a string variable @str and assigned a new name ‘ln2’ using the ALIAS column.

DECLARE @str VARCHAR(50)
SET @str = 'Learn everything for free' 
SELECT LEN (@str) AS 'ln2' 
ln2
-----------
25

How to Find the String Length with Trailing Spaces?

In the above two examples, the given string has no spaces, and the next two sections show the behaviour of the LENGTH function with leading and trailing spaces.

The example below shows whether the SQL LEN() function counts trailing spaces when calculating the string length.

SELECT LEN('Word') AS ln3;
SELECT LEN('Word    ') AS ln4;

As you can see from the output below, it is not counting the trailing spaces at the end of the string.

ln3
-----------
4

ln4
-----------
4

If you intend to find the length, including the trailing spaces, use the DATALENGTH function.

SELECT DATALENGTH('Word    ') AS ln5;
ln5
-----------
8

Check the SQL LEN() function counts leading spaces

In our previous example, we used the LEN() function against a normal string and text with trailing spaces; here, we will work on leading spaces. The query below checks whether the LEN() function counts the leading spaces when finding the string length or not.

The first statement shows the actual length without spaces, and the second one has four empty spaces before Game. In the last line, we added 4 spaces at the start and 3 at the end. As you can see from the output, it simply ignored the trailing spaces but counted the leading spaces.

SELECT LEN('Game') AS ln6;
SELECT LEN('    Game') AS ln7;
SELECT LEN('    Game   ') AS ln8;
ln6
-----------
4

ln7
-----------
8

ln8
-----------
8

Empty String

If you pass an empty string as an argument to the SQL LEN function to find the length, it returns zero as output. If you observe the queries below, they return zero for an empty string and a string with multiple spaces.

SELECT LEN('') AS es1;
SELECT LEN('  ') AS es2;
es1
-----------
0

es2
-----------
0

NULL values

When the LEN() function identifies a NULL value as an argument, it simply returns a NULL value as output. So, when the column has 100 rows and while finding the length, instead of returning an error, it shows NULL for the NULL row.

SELECT LEN(NULL) as es3;
es3
-----------
NULL

How to pass Numbers to the LEN function?

The LENGTH() function is not limited to the string expression or variable; you can use the INT or FLOAT values as an argument. If you pass an integer value, it simply counts the total digits in that number. However, for the float or decimal values, it counts the . as a character, so 1234.689 returns 8 (including .)

SELECT LEN(12345) AS n1;
SELECT LEN(1234.689) AS n2;
n1
-----------
5

n2
-----------
8

Unicode and Non-Unicode characters

The LEN() function returns the same length for both the Unicode and non-Unicode characters.

SELECT LEN('Hello') AS st1;
SELECT LEN(N'Hello') AS st2;
st1
-----------
5

st2
-----------
5

SQL LEN function on Table Columns

The LEN function also allows you to find the length of expressions inside the columns. In this SQL Server example, we will calculate the length of a [Department Name] and the Email address. Apart from them, we used the CONCAT function to combine FirstName and LastName, and then used the LEN() function to find the length of the combined name.

SELECT [FirstName]
      ,[LastName]
      ,CONCAT(FirstName, ' ',LastName) AS Name
      ,LEN(CONCAT(FirstName, ' ',LastName)) AS NameVal
      ,[DepartmentName]
      ,LEN([DepartmentName]) AS [DeptVal]
      ,[Email]
      ,LEN([Email]) AS [EmailVal]
 FROM [Employe]

SQL LEN Function in WHERE Condition

In this example, we use the string LEN function inside the WHERE Clause. For instance, the following query will check whether the length of a [Department Name] is greater than 10 or not. If the given condition is TRUE, the SELECT Statement will display the records. The LEN() function in the ORDER BY clause will sort the employee records based on the department length in descending order.

SELECT [FirstName]
      ,[LastName]
      ,[DepartmentName]
      ,LEN([DepartmentName]) AS [Dept Length]
      ,[Email]
      ,LEN([Email]) AS [Email Length]
 FROM [Employe]
 WHERE LEN([DepartmentName]) > 10
ORDER BY LEN([DepartmentName]) DESC
SQL Server LEN Function Example

Combining with Other functions

Apart from using the SQL Server LEN() function in SELECT, WHERE, and ORDER BY clauses, you can use it inside other functions to perform complex calculations. Here, we use the aggregate AVG function to find the average length of the department and email address.

SELECT AVG(LEN([DepartmentName])) AS AvgDept,

      AVG(LEN(Email)) AS AvgEmail FROM [Employe]
AvgDept     AvgEmail
----------- -----------
10          14

SQL LEN() function vs DATALENGTH()

When you are calculating the length of any string, people might confuse the LEN() and DATALENGTH() functions. Although they look similar, they are meant for different operations, and the table below shows some of the differences between the LEN() and DATALENGTH() functions.

LEN()DATALENGTH()
It returns the total number of characters in a string.It returns the total bytes required to store the expression.
It counts the leading spaces.It also counts the leading spaces.
The LEN() function ignores the trailing spaces.The DATALENGTH function considers the trailing spaces.
It returns a NULL value for NULL input.It also returns NULL as the output.
For Unicode and non-Unicode strings, it returns the same result.As Unicode requires more bytes to store than a non-Unicode string, the results are different.
It does not support text, ntext, and image data types.It supports text, ntext, and image data types.
Categories SQL