Monday, November 2, 2015

How to Find Length of String in SQL SERVER? LEN() Function Example

You can use 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. Remember 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 character, it can hold names with maximum 60 character long, but not all names are 60 character.  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.

This function is also explained in detail in my favorite book Microsoft SQL SERVER 2012 T-SQL Fundamentals, one of the best books to learn SQL SERVER in general and SQL SERVER 2012 particular.

How to Find Length of String in SQL Server using LEN()

LEN() function Example in SQL SERVER 2014

If you want to find out all names which are less than 10 character 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 return the length of values stored in that column.

Let's see 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 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.

How to use LEN function in SQL SERVER with example

Further Learning
Introduction to SQL by Jon Flanders
Introduction to SQL Server
Head First SQL

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:
  • 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: [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 Web sites to learn SQL online for FREE? (resource)

No comments :

Post a Comment