T-SQL String Functions: difference between using Left/Right and Substring and strange behaviour

Question

I am using SQL Server 2008 & 2005 (Express). I'm trying to extract part of an alpha numeric string from a varchar field.

RIGHT(str_field, 3) yields null values but SUBSTRING(str_field, LEN(str_field)-2, LEN(str_field)) gives the right value. LEFT(str_field, 7) gives the expected values. What gives?

I would have thought that RIGHT(str_field, 3) and SUBSTRING(str_field, LEN(str_field)-2, LEN(str_field)) are equivalent expressions.

Answer 1

You have trailing spaces

RIGHT will yield spaces but LEN ignores trailing spaces

DECLARE @foo varchar(100)
SET @foo = 'abc12345def   ' --3 spaces

--right or substring
SELECT RIGHT(@foo, 3)
SELECT SUBSTRING(@foo, LEN(@foo)-2, LEN(@foo))

--demonstrate you get spaces
SELECT REPLACE(RIGHT(@foo, 3), ' ', 'z') --single space

--length differences
SELECT LEN(@foo), DATALENGTH(@foo)

--solution
SELECT RIGHT(RTRIM(@foo), 3)
--or trim your column values before storing

See SET ANSI_PADDING

Note: you won't get NULL for non NULL input...

--only NULL if you send in NULL
SELECT RIGHT(NULL, 3)