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 LEN() and 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, ');
I’m not sure what were the reasons of this definition, but the moral is simple. I’ve ended up with a bug as I assumed it works the same as in JavaScript, but it weren’t. I cannot blame the documentation, as both functions work exactly as described (LEN and SUBSTRING). Always look into documentation, if you use something for the first time, even if it looks deceptively familiar!