SQL Server supports different data types to store relevant data in SQL tables. These data types can be integer, float, Varchar, NVARCHAR, Float, Char, bit. While we work with the string data types, it is common to find string length.
Suppose you have a web portal where users create their profiles. In the profile section, you might have fields such as [FirstName], [LastName], [Address]. You want to check the maximum string length for data stored in these columns.
SQL Server LEN() function returns the string length for the specified column as an input. This function is available from SQL Server 2008 onwards. These functions are available in Azure SQL Databases, Managed instances as well.
Syntax:
LEN ( string_expression )- Input: The string expression can be a constant, character, binary or variable data.
- Output: The output value data type is int with exception of the varchar(max), nvarchar(max), varbinary(max) that returns bigint.
Let’s explore the LEN() function usage with various examples.
Example 1: Getting the String Length
The following example returns the string length for the input string.
SELECT LEN('This is a sample text')
AS [StrLength]
Example 2: Getting the String Length Having Trailing Spaces
The customer might insert data with trailing space. If we calculate the string length, does it consider trailing spaces as well?
No, the SQL LEN() function avoid trailing spaces and returns the length of the actual string. The string has three trailing spaces; however, the LEN() returns string length as 8.
SELECT LEN('Rajendra ')
AS [StrLength]
Example 3: Getting the String Length Having Leading Spaces
In example 2, we saw that LEN() avoids trailing spaces. However, if we have another string with leading spaces, does it have the same behavior?
Yes, it counts leading spaces as well while calculating string length. As shown below, LEN() returns value 11.
SELECT LEN(' Rajendra')
AS [StrLength]
Example 4: Using the LEN() Function for String Columns
In the previous examples, we provided string inputs directly in the LEN() function. In a practical scenario, you use these SQL functions for data stored in SQL tables.
For example, in the below T-SQL script, we calculate string length for data stored in the [Name] column of [SalesLT] schema and [Product] table.
SELECT TOP (10) [ProductID]
,[Name]
,Len(SalesLT.Product.Name) AS StrLength
,[ProductNumber]
,[Color]
,[StandardCost]
FROM [SalesLT].[Product]
Example 5: Using the LEN() Function in the WHERE Clause
You can use the LEN() function where clause predicate to find records having a specific length. For example, the following T-SQL returns records with a string length from the Name column greater than 21.
SELECT TOP (10) [ProductID]
,[Name]
,[ProductNumber]
,Len(SalesLT.Product.Name) AS StrLength
,[Color]
,[StandardCost]
FROM [SalesLT].[Product]
WHERE Len(SalesLT.Product.Name) >21As shown below, In the output, we have data having a length greater than 21 characters.
Example 6: NULL Value in the LEN() Function
The LEN() function always returns NULL for an input value NULL.
Therefore, if you have a column with NULL values, you get NULL in the string length as well.
SELECT TOP (5) [CustomerID]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,LEN(SalesLT.Customer.Suffix) AS NULLValue
FROM [SalesLT].[Customer]
Example 7: Unicode Character String Input in the LEN() Function
The LEN() function works similarly for Unicode or Non-Unicode values. It returns the number of characters irrespective of single-byte (Non-Unicode) or double-byte(Unicode) values. As shown below, the function LEN() returns 8 characters for both inputs.
SELECT LEN('Rajendra') AS [Non-UnicodeStrLength]
SELECT LEN(N'Rajendra') AS [UnicodeStrLength]
Example 8: Variable Input in LEN()
SQL Server supports variables declarations to define a variable with supported data types for storing values. The variable can be the reference in the script for quick reference.
Therefore, if we declare a variable for string values, can we calculate the string length using LEN(). Yes, we can do as shown in the following example.
DECLARE @Name NVARCHAR(50)= 'Rajendra'
SELECT LEN(@Name) AS StrLength
Example 9: Using LEN() with varchar(max), nvarchar(max) or varbinary(max) Data Types
SQL Server LEN() function supports varchar(max), nvarchar(max) or varbinary(max) data types as well. However, the returns value will be in the bigint data type.
For example, the below T-SQL calculates column data length having varbinary(max) data type.
You cannot use the LEN() function for text, ntext and image data types. It gives the following error message.
CREATE TABLE Test
(
id int,
[sample] image
)
SELECT LEN([Sample]) FROM test
Viewing the Execution Plan of the Query with the LEN() Function
If you generate an actual execution plan of a SQL statement with Len() function, it shows a compute scalar operator.
Note: To generate the actual execution plan, press CTRL + M in SQL Server Management Studio before executing a query.
Click on the compute scalar and view its properties. You get details such as scalar operator details, estimated CPU, IO, number of executions, number of rows.
Comparing the SQL Server LEN() Function with DATALENGTH()
Sometimes, developers confuse with the use of the LEN() or DATALENGTH() function. Therefore, let me add a quick comparison of both functions.
| LEN() | DATALENGTH() |
| The LEN() returns the number of characters in a string. | It returns the number of bytes for storing the string expression. |
| It excludes the trailing spaces. | It does not exclude trailing spaces while calculating the number of bytes. |
| Its result does not depend on Unicode or Non-Unicode string. | The Unicode data types require two bytes storage while the Non-Unicode takes 1 byte. Therefore, the results are different for both data types.  |
| It returns NULL for the input value NULL. | It also returns NULL for the input value NULL.  |
| It does not support ntext, text and image data types for the input expression. | The DATALENGTH() function works with ntext, text and image data types as well. |
Conclusion
This article explores the SQL LEN() function for calculating string length in SQL Server using various use cases. We also compared the LEN() and DATALENGTH() function as per the following summary.
- Use LEN() for calculating the number of characters in the string expression.
- Use DATALENGTH() for calculating the number of bytes required for storing the string.
