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.