SQL Server LEN() Function
The SQL Server (Transact-SQL) LEN() function returns the number of characters of the specified string expression, excluding trailing spaces.
Note: To return the number of bytes used to represent the string expression, DATALENGTH() function can be used. It includes trailing spaces in the length calculation.
Syntax
LEN(string)
Parameters
string |
Required. Specify the string to return the length for. |
Return Value
Returns the number of characters of the specified string.
Example 1:
The example below shows the usage of LEN() function.
SELECT LEN('12345'); Result: 5 SELECT LEN('ABCDE'); Result: 5 SELECT LEN(12345); Result: 5 SELECT LEN('AlphaCodingSkills'); Result: 17 SELECT LEN('Alpha Coding Skills'); Result: 19 SELECT LEN(NULL); Result: NULL SELECT LEN(''); Result: 0 SELECT LEN(' '); Result: 0
Example 2:
Consider a database table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
The statement given below can be used to get the length of records of City column.
SELECT *, LEN(City) AS LEN_Value FROM Employee;
The query will produce the following result:
EmpID | Name | City | Age | LEN_Value |
---|---|---|---|---|
1 | John | London | 25 | 6 |
2 | Marry | New York | 24 | 8 |
3 | Jo | Paris | 27 | 5 |
4 | Kim | Amsterdam | 30 | 9 |
5 | Ramesh | New Delhi | 28 | 9 |
6 | Huang | Beijing | 28 | 7 |
❮ SQL Server Functions