Navigate back to the homepage

TIL - Comparing Strings with trailing spaces using TSQL

Mark Brown
December 2nd, 2021 · 1 min read

Summary 📖

I set off down this path of enlightenment as I investigated a customer issue that “could never happen”. I paused briefly to wipe the egg off my face then dug into how it could happen.

To keep my Car Garage analogy flowing, imagine you own a franchise that sells many flavours of car - BMW, Mercedes, and the like. The used car market is booming and you now automate the process of diverting incoming shipments to the right garage - for example you want all Mercedes to go to Garage A, All BMWs to Garage B, and so on (I’m really trying here..).

Now imagine you receive a shipment of fancy new Mercedes’, and you make a typo inputting those and instead input “Mercedes ” - to your shock these cars are not assigned to the right place 😱 🤯.

The Java developer in me looks at the 2 strings and thinks “But of course, they don’t match!“. To which the SQL developer in me now says - ah of course, ANSI/ISO SQL-92.

spec

So why does it do this? 🙋‍♂️

The ANSI/ISO SQL-92, for those that don’t know, requires padding for the strings used in comparisons so that their lengths match before comparing them. This padding affects the semantics of WHERE and HAVING clause predicates among other TSQL string comparisons. For example, TSQL would consider both 'A' and 'A ' to be the same for most comparison operations.

The only exception to this is the LIKE predicate, because the purpose of this predicate (by definition) is to facilitate pattern searches rather than equality tests.

tsql

TL;DR ⏭

1MBP:~ mtjb$ sqlcmd -b -S "127.0.0.1" -U sa -P 'password'
21>
31>
41> SELECT CASE WHEN 'A ' = 'A' THEN 1 ELSE 0 END
52> GO
6
7-----------
8 1
9
10(1 rows affected)
111>
121>
131> SELECT CASE WHEN ' A' = 'A' THEN 1 ELSE 0 END
142> GO
15
16-----------
17 0
18
19(1 rows affected)
201>
211>
221> SELECT CASE WHEN 'A ' LIKE 'A' THEN 1 ELSE 0 END
232> GO
24
25-----------
26 0
27
28(1 rows affected)
291>

More articles from Mark Brown

Automate away your N+1 problems with Hibernate Statistics

If you are using an ORM in your projects, The N+1 query problem is definitely one of your issues. If you don't know that yet - well, may the Lord have mercy on your soul.

November 29th, 2021 · 1 min read

Introduction to Indexes, and the Primary Key

Database indexing is a development task. The most important information for indexing is not the storage system, or the configuration of the server - but instead how the application queries the data. This knowledge is not easily obtained from DBAs or external consultants - so it's on us, the developers.

August 18th, 2021 · 4 min read
© 2020–2021 Mark Brown
Link to $https://twitter.com/marktjbrownLink to $https://github.com/mtjbLink to $https://instagram.com/marktjbrownLink to $https://www.linkedin.com/in/mark-brown-9952b4a8/