LIKE

Definition

LIKE tests whether a string matches a simple pattern using two wildcards: % (any sequence of zero or more characters) and _ (exactly one character).

Running example — a products(id, name, category, price, stock) table, where stock can be NULL (not yet counted):

sql
SELECT name FROM products WHERE name LIKE 'Pro%';   -- starts with 'Pro'
SELECT name FROM products WHERE name LIKE '%book%'; -- contains 'book' anywhere
SELECT name FROM products WHERE name LIKE '_ovel';  -- exactly 5 chars, ending in 'ovel'

'Pro%' matches 'ProLaptop' and 'ProPhone' (anything STARTING with 'Pro'); '%book%' matches 'Notebook' (containing 'book' anywhere); '_ovel' matches 'Novel' (exactly one character, then 'ovel').

Edge Cases and Pitfalls

• LIKE's case-sensitivity depends on the column's collation, exactly like ordinary comparison (12.4) — 'pro%' may or may not match 'ProLaptop' depending on that setting, not on LIKE itself.
• A leading % ('%book%') generally CANNOT use a normal index efficiently, since the database can't know where in the string to start looking — a trailing-only wildcard ('book%') can typically still use an index. This is a real, common performance consideration for large tables.
• To match a LITERAL % or _ character (not as a wildcard), most dialects support an ESCAPE clause: LIKE '50\%%' ESCAPE '\' to find strings starting with the literal text "50%".

Key Takeaways

• % matches any sequence of characters (including zero); _ matches exactly one character.
• Case-sensitivity follows the column's collation, not LIKE itself.
• A leading % defeats normal index usage — a real performance consideration on large tables.