LIKE & Wildcards
In this page:
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 defaultEscaping 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 '\\';Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: