LEN Function in SQL Server

by

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

Where you will learn to compute the length of the literal string, or the string containing the leading and trailing spaces.

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

LEN Function in SQL Server

The LEN() function in SQL Server calculates the total length in characters of the given string. But while computing the length of the string it doesn’t consider the trailing spaces of that string.

The syntax is given below.

LEN(str_exp)

Where,

  • LEN(): It is the function that returns the number of the character or length of the string.
  • str_exp: It is 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 trailing space of the strings.

But why use the LEN() function,

  • The LEN() function can be used for the data validation to make sure that the string meets specific length criteria. For example, you can put the maximum length the user’s name can have.
  • LEN() function helps in query optimization, if you have large datasets, knowing the length of the strings is very important for optimizing the queries. For example, you can perform a filter or join the tables based on the length of the string to improve the query performance.
  • LEN() function also helps in presenting the data in a specific way. You can truncate the strings based on the length for a visual look.

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 the string 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, you can use the LEN() function.

SELECT LEN('United States') LiteralStringLength;
Using LEN Function in SQL Server on Literal String

The are 13 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.

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

Using LEN Function in SQL Server on Literal String with Trailing Spaces

The literal string ‘USA ‘ contains the trailing spaces, but the LEN() function returns only 3 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('  USA') LiteralStringLength;
Using LEN Function in SQL Server on Literal String with Leading Spaces

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

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

Let’s move ahead and 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 Employees table which is shown below.

Using LEN Function in SQL Server on Employees Table Column

You have to filter customers by length which means finding customers with names than a certain length. Consider you have to find all the customers with first names containing more than 6 characters.

For that use the below query.

SELECT * FROM Employees
WHERE LEN(FirstName) > 6;
Using LEN Function in SQL Server on Table Column

Look at the above output of the query, it returns all customers whose first names contain more than 10 characters. For example, a customer with an ID equal to 3 has a first name, Maximilian, by looking at this name, you can see it contains more than 10 characters.

You can also combine the LEN() function with other functions, for example, you need to find the average length of first names of the customers. For that use the below query.

SELECT AVG(LEN(FirstName)) AS AvgLength
FROM Employees;
Using LEN Function in SQL Server on Table Column with Other Functions

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

Then the LEN() function returns the length of each first name of the customer and then the AVG() function takes all the length and computes the average of the length which is 5.

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 learned how to use the LEN() function on the table column.

You may also like to read: