# TSQL - LEN and SUBSTRING may seem bugged but they are not, they are only very strange

Post date: Dec 6, 2011 9:34:02 AM

I do not like the fact that LEN exclude trailing spaces but I dislike the behavior of SUBSTRING even more.

From BOL:

http://msdn.microsoft.com/en-us/library/ms187748.aspx

If *start_expression* is less than 1, the returned expression will begin at the first character that is specified in *value_expression*. In this case, the number of characters that are returned is the largest value of either the sum of *start_expression* + *length_expression* â€“ 1 or 0. If *start_expression* is greater than the number of characters in the value expression, a zero-length expression is returned.

This is crazy stuff if you are used to any other programming language.

Try to guess the outcome of the following query without looking at the results i got.

DECLARE @var **VARCHAR**(100)**SET** @var = ' peter '**SELECT** 'Len' **AS** expr, **LEN**(@var) as res **UNION** **ALLSELECT** 'Len(substring 9)', **LEN**(**SUBSTRING**(@var, 0, 9)) **UNION** **ALLSELECT** 'Len(substring len(@var))', **LEN**(**SUBSTRING**(@var, 0, **LEN**(@var))) **UNION** **ALLSELECT** 'Len(ltrim, rtrim)', **LEN**(**LTRIM**(**RTRIM**(@var))) **UNION** **ALLSELECT** 'Datalength', DATALENGTH(@var)

expr res **Len** 7**Len**(**substring** 9) 7**Len**(**substring** **len**(@var)) 6**Len**(**ltrim**, **rtrim**) 5 Datalength 9