LIKE & Wildcards

The Percent Wildcard

The LIKE operator is used to search for patterns in text columns, such as finding every email ending in a specific domain. We use the percent symbol as a wildcard. It represents zero, one, or multiple characters, so 'a%' matches anything starting with the letter a.

Example: The Percent Wildcard

SELECT * FROM users WHERE email LIKE '%@gmail.com';

The Underscore Wildcard

The underscore character is another wildcard, useful when you know exactly how many characters to expect. It represents exactly one single character. This is perfect when you know the exact length of the pattern you want to match, like a 4-digit product code.

Example: The Underscore Wildcard

SELECT * FROM products WHERE code LIKE 'A_23';

The NOT LIKE Operator

We can use NOT LIKE to find values that do not match our pattern, such as filtering out test accounts with a known naming convention. This helps exclude text with certain letters or domains from your results entirely.

Example: The NOT LIKE Operator

SELECT * FROM users WHERE email NOT LIKE 'test%';

Case Sensitivity with LIKE

By default, the LIKE operator in MySQL is case-insensitive under the standard collation, so Apple and apple both match. If you need to search for an exact case match, you can use the BINARY operator before the search string to force a byte-for-byte comparison.

Example: Case Sensitivity with LIKE

SELECT * FROM users WHERE name LIKE 'Apple'; -- matches 'apple' too, case-insensitive by default

Escaping Wildcards

Sometimes you want to search for the literal percentage or underscore characters instead of using them as wildcards, like matching a discount code that contains a real percent sign.

We can do this by using a custom escape character. This tells MySQL to treat them as normal letters rather than pattern symbols.

Example: Escaping Wildcards

SELECT * FROM coupons WHERE code LIKE '50\%OFF' ESCAPE '\\';