In this step by step SQL Tutorial I will show you how to use Len() function. For the purpose of this tutorial I will use SQL Server 2012.
Other Versions of SQL Server:
- SQL Server 2012
- This blog post has been written using SQL Server 2012
- SQL Server 2008 / R2
- The examples we used should work in SQL Server 2008 and R2
- SQL Server 2005
- We suspect that this will work in SQL Server 2005 as well
LEN Function - returns the number of characters of the specified string value.
LEN (<<String Value>>)
<<String Value>> - Provide string value (field name or expression) that will be used as your input.
In this example I will use AdventureWorksDW2012 database dbo.DimCustomer table and check how long is the email address.
Below is example of using LEN and you can see the results of LEN function using EmailAddress field.
The LEN () by default removes spaces from the right side but do not remove space from the left side.
In the first example the length of string is 5 and we get result 5.
In this example we have space at the beginning of the string and the results give us six as a length of string.
In the last example we have a space at the end of string and the length of string is 6 but len function ignores spaces at the end so the result is 5 not 6.
Recently I noticed that someone visited this page by typing can sql len function detect space and we have already provided the answer above so LEN ignores spaces at the end so it cannot detect it but what if you want to count the space the end?
One possibility is to use DATALENGTH function that returns 'size' of the string so if one character is 1byte than example below will give us 6 and it does take into account space at the end as it does take 1 byte.
SELECT DATALENGTH('kasia ')
NOTE: but be careful because this function returns size in bytes so if I provide unicode sting with N'kasia ' than each character is 2 bytes so result is 12 not 6.
SELECT DATALENGTH(N'kasia ')
I hope that helps