SQL LEN Function | What is SQL Server LEN() Function?

SQL LEN Function Summary: In this article, you will learn What is SQL Server LEN() function and How to use to determine the number of characters of an input string.

Definition and Usage

The SQL LEN function used to find the length of a specified expression. In other word you can say that The LEN Function will count the number of characters inside the given expression and its returns the INT results or returns the length of string. The LEN() Function is a String Function.

The LEN() function returns the number of characters of an input string, excluding the trailing blanks.

SQL LEN Function Syntax

The following syntax shows the LEN() function:

In this syntax:

• input_string: Required. this can be a literal character string, a column of either character or string expression or or binary data. therefore, If string is NULL, it returns NULL

The SQL LEN function returns a value whose data type is BIGINT if input_string is of the VARBINARY(max), VARCHAR(max), or NVARCHAR(max) data type, otherwise, INT.

Applies To

The SQL LEN function can be used in the following versions of SQL Server (Transact-SQL):

SQL Server 2005, SQL Server 2017, SQL Server 2008 R2, SQL Server 2008, SQL Server 2016, SQL Server 2012, and you can used in SQL Server 2014

SQL Server LEN function Examples

Let’s us see at some LEN function examples and explore how to use the LEN function in SQL Server (Transact-SQL).

A) Using LEN() function with a literal string

SELECT LEN('TutorialScan.com');

SELECT LEN('TutorialScan.com   ');
----(trailing spaces are not included in the calculation)
       
SELECT LEN('   TutorialScan.com');

SELECT LEN('   TutorialScan.com   ');
--(trailing spaces are not included in the calculation)
       
SELECT LEN(' ');
--(trailing spaces are not included in the calculation)
       
SELECT LEN('');

SELECT LEN(NULL);

Output:

B) Using SQL Server LEN() function with a column

The following given below Persons table to used for demonstration.

The following given below statement used the LEN() function to return the person name, its length, and sorts the name by the length of the person name.

SELECT
    Name,
    LEN(Name) person_name_length
FROM
    Persons
ORDER BY
    LEN(Name) DESC;

Output:

C) Using LEN Function For Another Example

These function is used to count the number of characters inside a specified expression. Therefore, this query uses the SQL ServerLEN function to determine the length of a string expression.

DECLARE @String_Expression varchar(80)
SET @String_Expression = 'Learn SQL Server Tutorial' 

SELECT LEN (@String_Expression) AS 'SQL Length' 

--Finding Length directly
SELECT LEN ('Learned SQL Server 2017') AS 'SQL Length'

Output:

EXAMPLE ANALYSIS
Within this Len function demonstration query, let’s declared a string variable and set the string data.

DECLARE @String_Expression varchar(80)
SET @String_Expression = 'Learn SQL Server Tutorial'

Therefore, we used LEN function in this example to find the length of a string variable @String_Expression and assigned a new name ‘SQL Length’ using SQL ALIAS.

SELECT LEN (@String_Expression) AS 'SQL Length'

Furthermore, in the next line, as you can see that I have used the LEN String Function directly on the string.

--Finding Length directly
SELECT LEN ('Learned SQL Server 2017') AS 'SQL Length'

D) Using LEN Function with WHERE Condition

In this example, we used the persons table data and also used the LEN function inside the SQL Where Clause.

For instance, The following query will check whether the length of a [Address] is greater than 8 or not. If the given condition is TRUE, then the SELECT Statement will display the records.

SELECT [Name],
       [Address],
       LEN([Address]) AS [Address Length],
       [City],
       LEN([City]) AS [City Length]
 FROM [Persons]
 WHERE LEN([Address]) > 6

Output:

Conclusion

In this tutorial, you have learned What is SQL Server LEN() function and How to use this function with different conditions to determine the number of characters of an input string. I hope you will enjoy! if have any query, contact me, I have try to resolve the problem.