The LEN function in SQL Server is used to return the number of characters in a given string expression. It is commonly used in SELECT statements to retrieve the length of a column's data, but can also be used in other parts of a SQL statement, such as in a WHERE clause to filter results based on the length of the data.

LEN syntax

The syntax for the LEN function is as follows:

LEN(expression)

where expression is the string value for which you want to return the length.

LEN example

For example, if you have a table called "employees" with a column called "name", you could use the following query to return the length of each employee's name:

SELECT name, LEN(name) as name_length
FROM employees;

This would return a result set with the name and the length of the name for each employee in the table. It is important to note that LEN function only works with CHAR, VARCHAR, NCHAR and NVARCHAR data types.

Students table:

The following select example returns the values from the state column and its length.

SELECT state, LEN(state)
FROM students;

Result: