SQL - LEN() Function

---

---

The SQL LEN() function is used to retrieve the length of the characters of a string. The LENGTH() is the synonym of the LEN() function i.e we can use either of these to calculate the length. It accepts a string value as a parameter and returns the number of characters present in a String. The number of characters will be the actual length of the given String.

If we pass a NULL value as an argument, this function will return NULL. The trailing spaces at the end of the string are not included in the length. However, leading spaces at the beginning of the string are included.

Syntax

Following is the syntax of the SQL LEN() function with a string −

LEN ( expression )  

Parameters

• expression − It is a string whose length is to be counted.

Return value

This function returns the length of the given string.

Example

In the following example,we are using the SQL LEN() function to retrieve the length of the String ‘TutorialsPoint’.

SELECT LEN('TutorialsPoint’') as Length_of_String;

Output

Following is the output of the above query −

+------------------+
| Length_of_String |
+------------------+
|               15 |
+------------------+

Example

If we pass an empty string as an argument to the function, this function returns zero.

In the following example, we are passing an empty string as an argument to the SQL LEN() function to get the length of the string.

SELECT LEN('') as Length_of_String;

Output

The above SQL query produces the following output −

+------------------+
| Length_of_String |
+------------------+
|                0 |
+------------------+

Example

If any argument passes as a NULL value as an argument to the function, it returns NULL.

In this example, we are passing a NULL value as an argument to the LEN() function to find the length of the given value.

SELECT LEN(NULL);

Output

On executing the above query, it will produce the following output −

+--------------+
| LEN(NULL)    |
+--------------+
|         NULL |
+--------------+

Example

You can pass a table column as an argument to the LEN() function to find the length of the content of the column. Assume we have created a table named CUSTOMERS using the CREATE statement as follows −

CREATE TABLE CUSTOMERS(    
   ID INT NOT NULL,    
   FIRST_NAME VARCHAR (20),
   LAST_NAME VARCHAR(20),
   AGE INT NOT NULL,    
   ADDRESS CHAR (25) ,    
   SALARY DECIMAL (18, 2));

Now let's insert four records into the customers table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES (2, 'Ramesh','KUMAR', 32, 'Ahmedabad', 2000.00 ); 
INSERT INTO CUSTOMERS VALUES (3, 'Khilan','Verma', 25, 'Delhi', 1500.00 ); 
INSERT INTO CUSTOMERS VALUES (3, 'kaushik','Gupta', 23, 'Kota', 2000.00 ); 
INSERT INTO CUSTOMERS VALUES (5, 'Chaitali','Pal', 25, 'Mumbai', 6500.00 );

The Following SQL query retrieve the length of the content of the FIRST_NAME column of Customers table −

SELECT ID, FIRST_NAME, LAST_NAME, LEN(FIRST_NAME) AS NAME_LENGTH FROM CUSTOMERS;

Output

After executing the above statement, it produces the following output −

+----+------------+-----------+-------------+
| ID | FIRST_NAME | LAST_NAME | NAME_LENGTH |
+----+------------+-----------+-------------+
|  2 | Ramesh     | KUMAR     |           6 |
|  3 | Khilan     | Verma     |           6 |
|  3 | kaushik    | Gupta     |           7 |
|  5 | Chaitali   | Pal       |           8 |
+----+------------+-----------+-------------+

Example

You can also pass the numeric value to the LEN() function. In this example, we are passing a numeric value 123455 to LEN() function to retrieve the length of the passed value.

SELECT LEN(123455);

Output

After executing the above statement, it produces the following output −

+----------------+
| LEN(123455)    |
+----------------+
|              6 |
+----------------+