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!