Level 9 · Stray spaces in states and cities

% matches any run of characters, including none. Its sibling _ matches exactly one character. That makes LIKE useful for shapes and lengths, not only for “contains” searches.

Patterns for length and position

SELECT COUNT(*) AS padded_states
FROM customers
WHERE state NOT LIKE '__';

That returns 29. Every US state code is two letters, so a value that is not exactly two characters has something extra, here a space before or after the code (’ MO’, ‘DC ’). NOT LIKE simply keeps the rows where the pattern does not match.

Position works the same way. ' %' means “a space, then anything”, so it finds leading spaces; '% ' finds trailing ones. On city those two patterns find 51 customers, such as ’ detroit ’ and ‘San Francisco ’. Other fixed shapes follow the same idea: 'WELCOME__' would match WELCOME plus exactly two more characters.

Tyler’s three conditions are alternatives, so they are joined with OR. There is no AND in the WHERE, so no parentheses are needed. The 14 customers with a NULL city match none of the city patterns; LIKE on NULL is unknown, like every comparison with NULL.

What patterns miss

LIKE is case-sensitive in DuckDB and Postgres, but _ matches any character, so a lowercase ‘ca’ is two characters and passes. The schema notes 45 messy states in total: these 29 padded ones plus 16 lowercase ones. A full cleanup compares the value with its cleaned version, which chapter 6 covers.

Common mistakes

• Writing state LIKE '_' for “two characters”. Each _ is one character, so you need two.
• Using = with wildcards. city = ' %' looks for the literal text space-percent and finds nothing.
• Forgetting that % also matches zero characters, so '% ' matches a single space too.

In SQL Server

LIKE, %, _ and NOT LIKE work the same. SQL Server also has character classes, which DuckDB’s LIKE does not: LIKE '[A-M]%' (starts with A to M) or LIKE '%[0-9][0-9]' (ends in two digits). Its default collation is case-insensitive, so LIKE 'ca' would also match ‘CA’ there.