LEN Function in SQL Server

The SQL LEN() function is used to retrieve the length of a string character. Length() is also called LEN. It accepts the string value as a parameter and returns the character present in the string. The number of characters returned is equal to the actual length of the given string.

In this SQL Server tutorial, you will learn about the LEN function in SQL Server to get the length of the string.

Also, you will understand how to use the LEN() function on the table column. Then, use the LEN() function with other functions.

Table of Contents

LEN Function in SQL Server

The LEN() function in SQL Server calculates the total length in characters of the given string. The syntax is given below.

LEN(str_exp)

Where,

LEN() – The function returns the number of the character or length of the string.
str_exp – The string value, expression, or column whose length you want to calculate.

Remember, the LEN() function includes the leading spaces but doesn’t include the strings trailing spaces.

Reason for Using LEN Function

This LEN() function can validate data with strings that have specific lengths. You can set the maximum length the user’s name can have.
If you have large datasets, knowing the length of the strings is very important for the queries. For example, you can perform a filter or join the tables based on the string length to improve the query performance.
The LEN() function also helps present the data in a specific way.

There can be different reasons for using the LEN() function; the above are just examples. Let’s see how to use the LEN() function with different examples.

Using LEN Function in SQL Server on Literal String

The literal string is enclosed with a quotation mark; for example, ‘United States’ is the literal string. If you want to know the number of characters in the literal string, use the LEN() function.

SELECT LEN('United States') LiteralStringLength;

Using LEN Function in SQL Server on Literal String

There are 12 characters in the literal string ‘United States’ that you can see in the above output. Two words, United and States, are made of 12 characters and 1 extra character, which is the space between those words. So, the total number of characters is 13.

The LEN() function doesn’t include the trailing spaces, so let’s check that using an example. Suppose you have the literal string ‘TECH ‘ with two trailing spaces.

The literal string ‘TECH ‘ contains the trailing spaces, but the LEN() function returns only 4 characters. So LEN() function excludes the trailing spaces.

Now, check with the leading spaces or the spaces at the beginning of the literal string. For that, use the below query.

SELECT LEN('   TECH') LiteralStringLength;

When you find the length of the literal string containing the leading spaces, the LEN() function considers those spaces while calculating the length of the string. So, in the above output, the total number of characters or lengths in a ‘ TECH’ string is 7.

This is how we can calculate the length of the literal string with leading and trailing spaces using the LEN function in SQL Server.

Let’s see how to use the LEN() function on the table column.

Using LEN Function in SQL Server on Table Column

You can use the LEN() function with a table column to find the length of the values in that column. Suppose you have the EmpDetails [Namely Employee Details] table; you have to filter Employee Name by length, which means finding EmpName with names longer than a certain length. For example, you must find all the EmpNames containing more than 4 characters. For that, use the below query.

SELECT * FROM EmpDetails
WHERE LEN(EmpName) > 4;

Look at the above output of the query. It returns all employees whose EmpName contains more than four characters. For example, an employee with an ID equal to 101 has the name Daisy. This name contains more than four characters.

You can also combine the LEN() function with other functions. For example, you must find the average length of Empname. For that, use the below query.

SELECT AVG(LEN(EmpName)) AS AvgLength
FROM EmpDetails;

From the above output, the average length of the first name in the Employees table is 4 characters.

Then, the LEN() function returns each employee’s length, and then the AVG() function takes all the length and computes the average length, which is 4.

Now, I hope that you have a clear understanding of the LEN function in SQL Server.

Conclusion

In this SQL Server tutorial, you learned about calculating the length of the string in characters using the LEN() function in SQL Server and then computing the length of the literal string with leading and trailing spaces. Finally, you learned how to use the LEN() function on the table column.