SQL SERVER WHERE clause returns match when field contains trailing space

It’s nice to know you can still discover new things after 20 years in the industry.

Last week while investigating a defect of some data import work I’d done in SQL SERVER, one of my initial steps was to query the database for the imported records. For example:

SELECT * FROM [MyTable] WHERE [SomeField] = ‘SomeValue’;

That’s a standard, simple query. Nothing tricky about it.

However, what I had already discovered from inspecting the value returned to the application that uses it, is my value actually had a trailing space (i.e. ‘SomeValue ‘).

What surprised me was when I ran the query above it still returned the record.

That is: [SomeField] actually equals ‘SomeValue ‘ (with a trailing space) in the database record, yet my comparison to ‘SomeValue’ (no trailing space) still returned the record.

It turns out this is a quirk of SQL SERVER’s implementation of SQL.
It also turns out in all my years working with databases I must have been cleansing my data enough to never encounter this (in this instance I was generating SQL statements in Excel and was explicitly told not to touch import values [some of which included an incorrect trailing space]).

Check out this StackExchange question for more information.