Verzeihung, dieser Artikel ist derzeit nicht auf Deutsch verfügbar.

SQL usually ignores trailing spaces in string comparisons.  For example,

'A   ' = 'A'

is a true statement.

Likewise, if you have values 'A', 'A ', and 'A ' and you GROUP BY their column, these three different values will be counted as the same.

This loose matching also applies to IN() filters, e.g.

IN('A')

will match on 'A', 'A ', 'A ', etc.

If you specifically want to find records where the value is 'A' or 'A ' and not anything longer like 'A ', one simple solution is to include the BINARY keyword in your WHERE clause to force a binary comparison:

WHERE BINARY col = 'A'
WHERE BINARY col IN('A')

The caveat of this solution however, is that matches are case-sensitive.  If you want to keep case-insensitivity, you can use LIKE without a wildcard:

SELECT 'A'  LIKE 'A'  ; -- Returns 1 (True)
SELECT 'A'  LIKE 'A  '; -- Returns 0 (False)
SELECT 'A ' LIKE 'A ' ; -- Returns 1 (True)
SELECT 'A ' LIKE 'A  '; -- Returns 0 (False)

Another thing you can do if you want to get all matches of 'A' with or without trailing spaces but group results by lengths of trailing spaces, is throw BINARY into your GROUP BY clause:

SELECT COUNT(*), col
FROM records
WHERE col = 'A'
GROUP BY BINARY col

Results might look something like:

COUNT(*)	col
53	'A'
75	'A '
98	'A '
28	'A '

Leading spaces are not ignored.  If you want to ignore them, then you will have to resort to something like TRIM(col) = 'A' or a regular expression.

---

I got thinking about this topic because one of the projects I maintained until recently relied on Advantage Database Server^[1], which is a wonky SQL language with odd quirks you don't find in modern SQL languages.  One quirk that sticks out like a sore thumb is that when a string is inserted and it doesn't fill its column limit, no string terminator is recorded, such that when the string is retrieved it comes back right-padded with spaces to the maximum field length.  For example, if your column type is VARCHAR(10) and you insert "Billy", it comes back as "Billy " when retrieved.  If you don't want the excess spaces, you have to apply the TRIM() function when you retrieve the data, either in the query or in the application requesting the data.

A consequence of this behavior is that my predecessors on the project riddled the WHERE clauses of their queries with TRIM() functions, e.g.

SELECT * FROM Users
WHERE TRIM(first_name) IN('Tom', 'Dick', 'Harry');

AdvantageDB runs pretty damn slow already, so I wondered if the TRIM() function was really necessary with the IN() clause.  I ran some tests and found trimming strings for comparisons to be unnecessary.  Ignoring trailing spaces for comparisons is afterall part of the SQL standard.

Unlike other languages however, in AdvantageDB 'A' LIKE 'A ' is a true statement, probably because 'A' is always stored in VARCHAR(10) as 'A '.

---