MSSQL SUBSTRING() and LEN() Gotchas
Autocompletion is not always a good thing if you use something for the fist time and after seeing similar name you think it will behave as in all other languages, but in T-SQL it is not always true.
I was creating a dynamic SQL in stored procedure in SQL Azure and assumed that
SUBSTRING() will work in the way I think they should. I was wrong.
My goal was to remove the
", " part after creating optional elements in query. I thought that something similar to below construct (of course the strings were variables) will give me the right answer:
SET @test = SUBSTRING('ABCDEF, ', 0, LEN('ABCDEF, ') - 2);
I was wrong. Instead of
ABCDEF I got
ABCD. After short research
LEN() is returning the length of the string without trailing spaces. Come on, why?? But that is not all.
`SUBSTRING() takes a start position and length, but start position is 1 based, and if you use 0 as I did, it makes a special case which returns length - 1 characters starting from first one. So the proper version is:
SET @test = SUBSTRING('ABCDEF, ', 1, LEN('ABCDEF, ') - 1);
but below works too:
SET @test = SUBSTRING('ABCDEF, ', 0, LEN('ABCDEF, ');