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 7Len(substring 9) 7Len(substring len(@var)) 6Len(ltrim, rtrim) 5 Datalength 9