SqlServer LEN gotcha

A bug was reported where matching condition in the table join was produce erroneous matches and in this case this cause an invalid match which then caused the system to report records were not completed or available.

Here is an example setup and results

DECLARE @name1 NVARCHAR(200) = 'ABCDE FGHIJ'
DECLARE @name2 NVARCHAR(200) = 'ABCDEFGHIJ'
DECLARE @tokenSize INT = 6
DECLARE @tokenCompareSize INT
DECLARE @nameToken NVARCHAR(200)

SELECT @nameToken = UPPER(RTRIM(LEFT(@name1, @tokenSize)))
SELECT UPPER(RTRIM(LEFT(@name2, LEN(@nameToken))))

What happens is the token ABCDE gets trimmed down to length of 5 because LEN excludes trailing white spaces and so the above input of 'ABCDEFGHIJ' turns into ABCDE which means it is the same as 'ABCDE FGHIJ'

👻Ghost matches👻