How to find Length of String in SQL Server? LEN() Function Example

One of the most common task while writing SQL queries or stored procedure is to find the length of String. Since most of the columns are VARCHAR, you often need to find the length before taking any action. In Java, you can find the length of String by using the length() method but how about SQL Server? How will you find the length of String in Microsoft SQL Server in general and Microsoft SQL Server 2016 in particular? Well, you can use the LEN() function to find the length of a String value in SQL Server, for example, LEN(emp_name) will give you the length of values stored in the column emp_name. This method exists from SQL Server 2008 onwards which means you can use this function in SQL Server 2012, 2014, 2016 and latest version of Microsoft SQL Server i.e. SQL Server 2017.


Btw, you should remember that this is different than the length of the actual column which you specify while creating table e.g. emp_name VARCHAR(60).

To give you an example, you have a column called emp_name VARCHAR(60), which means the length of this column is 60 characters, it can hold names with maximum 60 characters long, but not all names are 60 characters long, hence you may need to find the length of actual String at times.

 In this SQL Server tutorial, I will teach you how to use the LEN() function to find out actual length or number of character in a VARCHAR or CHAR field.

LEN() function Example in SQL Server 2014

If you want to find out all names which are less than 10 characters than you can use LEN() function like following SQL query:

SELECT emp_name from Employee where LEN(emp_name) < 10

This will return all employee whose name is less than 10 character. LEN() is a very useful method, it takes column name and returns the length of values stored in that column.


Let's see a couple of more example of LEN() function in SQL Server 2008 and 2014:

1) Write an SQL query to find out all employees whose name is greater than 10 characters but less than 50 character

SELECT name from Employee where LEN(name) > 10 and LEN(name) < 50

You can also apply conditional logic using a CASE statement to do different things based upon different length e.g. you can shorten long name by appending "..." at the end of String.

SELECT CASE WHEN LEN (emp_name) <= 50 
THEN emp_name
ELSE LEFT(emp_name, 60) + '...'
END As emp_name
FROM Employee

So remember to use LEN() function in SQL Server to find out the length of any String stored in VARCHAR column. It doesn't need to be VARCHAR, but LEN() function accepts a text value, which means it could be CHAR, VARCHAR, NCHAR or NVARCHAR as well.

Related SQL Server tutorials

If you like this short and sweet tutorial and looking for some more SQL SERVER gems then check out my following post,  you may like them as well:

• 5 Free Oracle and SQL Server courses for Programmers (courses)
• How to replace NULL with empty String in SQL Server? (tutorial)
• SQL Server JDBC Error: The TCP/IP connection to the host Failed (guide)
• java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver [solution]
• java.sql.SQLException: No suitable driver found for JDBC:jtds: SQL server [solution]
• How to split String in SQL SERVER 2008? (answer)
• The difference between char, varchar, nchar and nvarchar in SQL Server? (answer)
• How to join more than two tables in one SQL query? (solution)
• How to create an Identity column in SQL SERVER? (example)
• 5 tips while migrating from Oracle to SQL SERVER? (tips)
• How to find the second highest salary of an employee in SQL Server? (query)
• What is the difference between WHERE and HAVING clause in SQL Server? (answer)
• How to find duplicate records from a table? (solution)
• 5 Websites to learn SQL online for FREE? (resource)
• Top 5 Courses to learn Database and SQL Online (courses)