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(): 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;
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.
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;
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.
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;
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;
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.
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:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.