SQL Server LEN() Function

SQL SERVER LEN() function calculates the number of characters of an input string, excluding the trailing spaces.

The LEN function in the SQL Server fetches the number of characters in a string. It counts the preceding spaces but not the trailing spaces.

For eg,

• ‘SQL SERVER’ has 10 characters
• ‘SQL SERVER ‘ also has 10 characters as the LEN function ignores the spaces at the end of the string.

It returns NULL for NULL string values. It is used in validating string lengths, padding strings to a certain length, or processing strings based on their length

LEN(input_string)

Parameter:

• input_string – It is an expression that can be a constant, variable, or column of either character or binary data.

Returns: It returns the number of characters of an input string, excluding the trailing spaces.

Let’s look at some examples of the LEN function in SQL Sever and understand it works.

Return the length of a string using LEN function Example

In this example, we will use the SQL LEN function on a string to get its length.

Query:

SELECT LEN('w3wiki');

Output :

13

Check if LEN() Function count trailing spaces or not Example

In this example, we will use the SQL LEN function on a string with trailing spaces and check the output.

Query

SELECT LEN('GFG       ') 
AS Length_with_trailing_spaces;

Output :

Check if LEN() Function count leading spaces or not Example

In this example, we will use the LEN function to check if it counts the leading spaces in a string.

Query:

SELECT LEN('       GFG') 
AS Length_with_leading_spaces;

Output:

Using LEN() function on a column Example

Consider the following table, in this example, we will use the SQL LEN function on the entire column of a table in SQL Server.

Table — Player_Details

Query:

SELECT PlayerName, LEN(PlayerName) PlayerName_Length
FROM Player_Details

Output :

• The LEN() function returns the number of characters in a given string expression, excluding any trailing spaces.
• It returns an INT data type, except for VARCHAR(max), NVARCHAR(max), or VARBINARY(max) which returns BIGINT.
• It includes Leading spaces in the length calculation, but not trailing spaces.
• If the input string is NULL, LEN() will return NULL.
• To include trailing spaces in the length calculation, use the DATALENGTH() function instead.