LEN Function in SQL Server 2012

How to find the length of the text of a field in a table using the LEN() Function.

In this article, I will explain how to find the length of the text of a field in a table. For this purpose we can use the LEN function in SQL Server 2012. The Length Function in SQL Server 2012 returns the number of characters in a string. The Length function does not count any blanks after the last character (trailing blanks). 

The following examples clearly show the concept of the Length function:

 LEN Function Example   Output
 SELECTLEN('Rahul')    5
 SELECTLEN('Rahul  ')  5
 SELECTLEN('  Rahul')  7

Len() function in SQL 2012

Syntax

SELECT LEN(column_name) FROM table_name

Let's have a look at a practical example. We create a table.

Creating table

CREATE TABLE SHARMA(

    id          INTEGER NOT NULL PRIMARY KEY,

    first_name  VARCHAR(10),

    last_name   VARCHAR(10),

    salary      DECIMAL(10,2),

    city        VARCHAR(20),  

 )

Now insert some values in the table.

 INSERT INTO SHARMA VALUES (2, 'Mohit',  'sharm',4789,'Agra');

 GO

 INSERT INTO SHARMA VALUES (4, 'Rahul' ,  'sharm',   5567,'London');

 GO

 INSERT INTO SHARMA VALUES (5, 'prabhat',  'kumar',  4467,'Bombay');

 GO

 INSERT INTO SHARMA VALUES (6, 'ramu',  'kksingh',  3456, 'jk');

 GO

 INSERT INTO SHARMA VALUES (8, 'MOHIT',  'SHARMA',  5000, 'KASGANJ');

 select * from SHARMA

The table look like this:

 Clipboard24.jpg

Figure 1

Now we want to select the length of the values in the "first_name" column above.

We use the following SELECT statement with the LEN Function:

SELECT LEN(first_name) as Lengthoffirst_name FROM table_name

The result look like this:

 Clipboard16.jpg

Figure 2

We can use the LEN Function with a Where condition:

SELECT LEN(first_name)as Lengthoffirst_name FROM sharma

where ID = 8

 Clipboard50.jpg

Figure 3