I had a small "issue" in one of my application this morning in a feature that was working no later than yesterday. The strange thing is that there were no updates to the application or the database in the last month. So I had no choice then to roll up my sleeves and dig into it! I finally found that the issue was related to an odd side effect of the LEN function in T-SQL. To better explain the problem, consider this small example:
DECLARE @x VARCHAR(20) DECLARE @y VARCHAR(20) SET @x = '1234567890' SET @y = '1234567890 '
All it does is that it declares 2 variables, set a value to them (same value but one as a space at the end. Now, if you run this little query:
SELECT 'x' AS Variable, '-' + @x + '-' AS Value , LEN(@x) AS LEN , DATALENGTH(@x) AS DATALENGTH UNION SELECT 'y', '-' + @y + '-', LEN(@y), DATALENGTH(@y)
You will get this result:
Variable Value LEN DATALENGTH -------- ---------------------- ----------- ----------- x -1234567890- 10 10 y -1234567890 - 10 11
What this query demonstrates is that the LEN() function ignores trailing spaces by design. It's very handy to check if there are only spaces in a column/variable. In short, DATALENGTH returns the length of the string in bytes including trailing spaces while LEN returns the length in characters excluding trailing spaces. Consider now this other query:
SELECT 'x' AS Variable, RIGHT(@x, LEN(@x) - 2) AS Right_Len , RIGHT(@x, DATALENGTH(@x) - 2) AS Right_DataLength UNION SELECT 'y', RIGHT(@y, LEN(@y) - 2), RIGHT(@y, DATALENGTH(@y) - 2)
Can you guess what the results will be? Do you think there will be any differences? There is one subtle difference. See the results:
Variable Right_Len Right_DataLength -------- -------------------- -------------------- x 34567890 34567890 y 4567890 34567890
The side effect to this comes when you mix the LEN function (which excludes trailing spaces) with other functions that don't exclude them. I will never forget that one!