MySQL Like

Why LIKE Instead of Equals

The equality operator only returns rows where a column matches a value exactly, character for character. WHERE country = 'India' will never match 'india' typed in a different case under a case-sensitive collation, and it can never match part of a longer string. The LIKE operator solves this by letting you describe the shape a string should have, rather than the exact string itself. It's used inside a WHERE clause in exactly the same position you'd otherwise use =, and it returns true or false for every row just like any other comparison.

The Percent Wildcard (%)

% stands for zero or more characters of any kind. Placed at the end of a pattern, 'Jo%' matches 'Jo', 'John', 'Joanna', and 'Jordan' - anything that begins with 'Jo'. Placed at the start, '%son' matches anything ending in 'son', such as 'Anderson' or 'Wilson'. Used on both sides, '%oh%' matches any value containing 'oh' anywhere at all.

SELECT employee_id, first_name, last_name
FROM employees
WHERE first_name LIKE 'Jo%';

The Underscore Wildcard (_)

_ stands for exactly one character - no more, no less. The pattern 'A___9' matches five-character codes that start with A, end with 9, and have any three characters in between, such as 'A1239' or 'AXYZ9', but it will not match a four-character code like 'A19'. Underscores and percent signs can be mixed freely in the same pattern.

SELECT product_id, product_code
FROM products
WHERE product_code LIKE 'A___9';

• LIKE is case-insensitive under MySQL's common ci (case-insensitive) collations, but case-sensitive under a _bin collation.
• % and _ only carry special meaning inside a LIKE pattern - in a plain column = '50%' comparison they're ordinary characters.
• A pattern with no wildcards at all, like LIKE 'Delhi', behaves exactly the same as = 'Delhi'.
• NOT LIKE reverses the match, returning rows that do not fit the pattern.

SELECT product_id, product_name, origin
FROM products
WHERE origin NOT LIKE 'Imported%';