> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL LIKE > How the MySQL LIKE operator matches a pattern with % and _, how to match those characters literally, and why LIKE ignores letter case by default. All examples on this page work on VillageSQL. Install Now → `=` matches a whole value. `LIKE` matches a pattern, so you can ask for the titles that start with a word, end with one, or have a given shape. Open the client with `mysql -u root -p sakila` before you start. ## How LIKE works ```sql theme={null} SELECT COLUMN_LIST FROM TABLE_NAME WHERE COLUMN_NAME LIKE 'PATTERN_TEXT'; ``` Replace `PATTERN_TEXT` with the pattern to match. A pattern is ordinary text plus 2 special characters: | Character | Matches | | - | - | | `%` | any run of characters, including none at all | | `_` | exactly 1 character | Everything else in the pattern matches itself. A pattern with neither `%` nor `_` behaves almost like `=`. The 2 part company on trailing spaces: under a `PAD SPACE` collation such as `utf8mb4_general_ci`, `'a ' = 'a'` is true and `'a ' LIKE 'a'` is false. Under a `NO PAD` collation, which every `utf8mb4_0900` collation is, both are false. ## Examples ### 1) Starts with ```sql theme={null} SELECT title FROM film WHERE title LIKE 'ACADEMY%' ORDER BY title; ``` ```text theme={null} +------------------+ | title | +------------------+ | ACADEMY DINOSAUR | +------------------+ 1 row in set (0.00 sec) ``` ### 2) Ends with ```sql theme={null} SELECT title FROM film WHERE title LIKE '%GOLDFINGER' ORDER BY title; ``` ```text theme={null} +----------------------+ | title | +----------------------+ | ACE GOLDFINGER | | BREAKFAST GOLDFINGER | | SILVERADO GOLDFINGER | +----------------------+ 3 rows in set (0.00 sec) ``` ### 3) A fixed number of characters 3 underscores after the `J` ask for a name of exactly 4 characters. ```sql theme={null} SELECT first_name, last_name FROM actor WHERE first_name LIKE 'J___' ORDER BY first_name, last_name; ``` ```text theme={null} +------------+-------------+ | first_name | last_name | +------------+-------------+ | JADA | RYDER | | JANE | JACKMAN | | JEFF | SILVERSTONE | | JOHN | SUVARI | | JUDE | CRUISE | | JUDY | DEAN | +------------+-------------+ 6 rows in set (0.00 sec) ``` `JOHNNY` is not in the list, because `J___` matches 4 characters and no more. ### 4) Does not match ```sql theme={null} SELECT title FROM film WHERE title NOT LIKE 'A%' ORDER BY title LIMIT 5; ``` ```text theme={null} +---------------------+ | title | +---------------------+ | BABY HALL | | BACKLASH UNDEFEATED | | BADMAN DAWN | | BAKED CLEOPATRA | | BALLOON HOMEWARD | +---------------------+ 5 rows in set (0.00 sec) ``` ## LIKE ignores letter case here The same pattern in lower case returns the same row. ```sql theme={null} SELECT title FROM film WHERE title LIKE 'academy%' ORDER BY title; ``` ```text theme={null} +------------------+ | title | +------------------+ | ACADEMY DINOSAUR | +------------------+ 1 row in set (0.00 sec) ``` That is not a rule about `LIKE`. It is a rule about the column, whose collation decides whether 2 strings count as equal. Sakila's is case-insensitive, and so is the MySQL default. The [Case sensitivity](/docs/tutorial/case-sensitivity) lesson shows how to check a column and how to ask for a case-sensitive match. ## Matching a literal % or \_ A `%` in your search text is a wildcard unless you say otherwise. `ESCAPE` names a character that turns off the one after it, so a pattern can ask for a real percent sign. ```sql theme={null} SELECT 'axxb' LIKE 'a%b' AS wildcard, 'axxb' LIKE 'a!%b' ESCAPE '!' AS literal_percent; ``` ```text theme={null} +----------+-----------------+ | wildcard | literal_percent | +----------+-----------------+ | 1 | 0 | +----------+-----------------+ 1 row in set (0.00 sec) ``` `a%b` matched `axxb` because `%` stood for `xx`. `a!%b` asked for a literal percent sign between `a` and `b`, which `axxb` does not have. ```sql theme={null} SELECT '50%' LIKE '50!%' ESCAPE '!' AS literal_percent; ``` ```text theme={null} +-----------------+ | literal_percent | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) ``` The underscore works the same way. ```sql theme={null} SELECT 'a_b' LIKE 'a!_b' ESCAPE '!' AS underscore, 'axb' LIKE 'a!_b' ESCAPE '!' AS any_char; ``` ```text theme={null} +------------+----------+ | underscore | any_char | +------------+----------+ | 1 | 0 | +------------+----------+ 1 row in set (0.00 sec) ``` Any character you are sure your data does not contain will do. Without `ESCAPE`, a backslash escapes the next character, so `'a\%b'` means the same thing. That shortcut stops working when the server runs with `NO_BACKSLASH_ESCAPES` in its `sql_mode`, and `ESCAPE` works either way. ## A leading % is the expensive one `LIKE 'ACADEMY%'` can use an index on the column, because every match starts with the same known text and an index is held in sorted order, so the server jumps to that part of it. `LIKE '%GOLDFINGER'` gives it no starting point, because a match can begin anywhere, so it reads every value in the column and tests each one. `EXPLAIN` on the 2 queries shows the difference: 1 estimated row against 1,000. On 1,000 films nobody notices. On a large table it is the difference between a lookup and a full scan. When you need to search inside text at scale, a different tool fits: see [Full-text search in MySQL](/docs/guides/full-text-search). ## Summary * Use `LIKE` to match a pattern rather than a whole value. * Read `%` as any run of characters and `_` as exactly 1 character. * Name an escape character with `ESCAPE` to match a literal `%` or `_`. * Expect the column's collation, not `LIKE`, to decide whether case matters. * Avoid a leading `%` on a large table, because no index can narrow the search. ## See also * [REGEXP](/docs/tutorial/regexp) — patterns that `%` and `_` cannot describe * [Case sensitivity](/docs/tutorial/case-sensitivity) — why the lower case pattern matched * [MySQL string functions](/docs/guides/string-functions) — reshaping text before you compare it * [Full-text search in MySQL](/docs/guides/full-text-search) — searching inside text at scale