It is very small point but developers are unaware of it that's why i am sharing it here.
Len function in sql server returns total no of characters in any string which is passed to it as a parameter,but it excludes trailing blanks
select LEN('abc '),LEN(' abc')
the output of above given query is
(No column name) (No column name)
It works same with char and varchar both datatypes.
SQL Server Tips · TSQL Tips · · ·
Hi Alok Chandra,
Nice Observation. you can use LTRIM function to work around this.
SELECT LEN('abc ') as A, LEN(LTRIM(' abc')) as B
thanks for sharing your thoughts.
That depends on what you want. Eg if you want the real length you have to use something like this.
SELECT LEN(field+'A')-1 as A
If you need trailing spaces, use DATALENGTH() instead of LEN()
Hi Marc Jellinek,
Yes your correct.Thanks for your comment
Hi Johan parieviet,
Thanks for sharing your thoughts.
And also you can use DATALENGTH() function [ As for Marc Jellinek comment]
DATALENGTH() will work for String but not for INT as string datatype occupies 1byte for each character whereas INT occupies 4 bytes for any number...
@Vipul, the thread is about strings, not ints. As you note, an int is always going to take up 4 bytes, there is no reason to use LEN() or DATALENGTH() on an int.