Just Code‎ > ‎

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

posted Dec 6, 2011, 1:34 AM by Peter Henell   [ updated Jan 16, 2012, 1:29 PM ]
I do not like the fact that LEN exclude trailing spaces but I dislike the behavior of SUBSTRING even more.
From BOL:
 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.

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

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