> ## 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 REGEXP > How to match a regular expression in MySQL with REGEXP, RLIKE and REGEXP_LIKE, the anchors and classes worth knowing, and how to force a case-sensitive match. All examples on this page work on VillageSQL. Install Now → `LIKE` describes a shape with 2 characters. `REGEXP` describes one with a whole pattern language, so it can ask for things `LIKE` cannot express: a choice between alternatives, a repeated group, a character range. Open the client with `mysql -u root -p sakila` before you start. ## How REGEXP works ```sql theme={null} SELECT COLUMN_LIST FROM TABLE_NAME WHERE COLUMN_NAME REGEXP 'PATTERN_TEXT'; ``` Replace `PATTERN_TEXT` with the regular expression to look for. The condition is true when the pattern matches anywhere inside the value. That is the first difference from `LIKE`, which has to describe the whole value. The pieces used below: | Piece | Means | | - | - | | `^` | the start of the value | | `$` | the end of the value | | `.` | any 1 character | | `*` | the thing before it, repeated any number of times | | `[AB]` | any 1 of the characters in the brackets | | `(AL\|BE)` | either alternative | ## Examples ### 1) Starts with ```sql theme={null} SELECT title FROM film WHERE title REGEXP '^ACA' ORDER BY title; ``` ```text theme={null} +------------------+ | title | +------------------+ | ACADEMY DINOSAUR | +------------------+ 1 row in set (0.01 sec) ``` Without `^` the pattern would match `ACA` anywhere in the title. ### 2) Ends with ```sql theme={null} SELECT title FROM film WHERE title REGEXP 'GOLDFINGER$' ORDER BY title; ``` ```text theme={null} +----------------------+ | title | +----------------------+ | ACE GOLDFINGER | | BREAKFAST GOLDFINGER | | SILVERADO GOLDFINGER | +----------------------+ 3 rows in set (0.00 sec) ``` ### 3) Either of 2 beginnings ```sql theme={null} SELECT first_name FROM actor WHERE first_name REGEXP '^(AL|BE)' ORDER BY first_name LIMIT 5; ``` ```text theme={null} +------------+ | first_name | +------------+ | AL | | ALAN | | ALBERT | | ALBERT | | ALEC | +------------+ 5 rows in set (0.00 sec) ``` `ALBERT` appears twice because 2 actors share the name. `SELECT` reports rows, not distinct values. ### 4) A shape LIKE cannot describe This asks for titles that begin with `A` or `B` and end with `S`. ```sql theme={null} SELECT title FROM film WHERE title REGEXP '^[AB].*S$' ORDER BY title LIMIT 5; ``` ```text theme={null} +----------------------+ | title | +----------------------+ | ADAPTATION HOLES | | AMELIE HELLFIGHTERS | | AMERICAN CIRCUS | | ANACONDA CONFESSIONS | | ANALYZE HOOSIERS | +----------------------+ 5 rows in set (0.00 sec) ``` ### 5) Everything that does not match `NOT REGEXP` reverses the test, the same way `NOT LIKE` does. ```sql theme={null} SELECT title, rating FROM film WHERE title NOT REGEXP '^A' ORDER BY title LIMIT 5; ``` ```text theme={null} +---------------------+--------+ | title | rating | +---------------------+--------+ | BABY HALL | NC-17 | | BACKLASH UNDEFEATED | PG-13 | | BADMAN DAWN | R | | BAKED CLEOPATRA | G | | BALLOON HOMEWARD | NC-17 | +---------------------+--------+ 5 rows in set (0.00 sec) ``` A NULL value matches neither `REGEXP` nor `NOT REGEXP`, because both return NULL for it. [IS NULL](/docs/tutorial/is-null) covers that. ## RLIKE is the same operator ```sql theme={null} SELECT title FROM film WHERE title RLIKE '^ACA' ORDER BY title; ``` ```text theme={null} +------------------+ | title | +------------------+ | ACADEMY DINOSAUR | +------------------+ 1 row in set (0.00 sec) ``` `RLIKE` and `REGEXP` are 2 spellings of one operator. Pick one and keep to it. ## Case, and how to insist on it A pattern follows the collation of what it is matching, the same as `LIKE`. So a lower case pattern matches upper case text by default. `REGEXP_LIKE()` is the function form of the operator, and its third argument overrides that: `'c'` means case-sensitive. ```sql theme={null} SELECT REGEXP_LIKE('ACADEMY DINOSAUR', '^aca') AS default_mode, REGEXP_LIKE('ACADEMY DINOSAUR', '^aca', 'c') AS case_sensitive; ``` ```text theme={null} +--------------+----------------+ | default_mode | case_sensitive | +--------------+----------------+ | 1 | 0 | +--------------+----------------+ 1 row in set (0.00 sec) ``` ## Prefer LIKE when LIKE can say it No index can narrow a `REGEXP` search, because a match may start anywhere, so the server tests every value. `LIKE` with a pattern that starts with fixed text can jump straight to the matching part of an index. When both can express the filter, `LIKE` is the cheaper of the 2. The [LIKE](/docs/tutorial/like) lesson explains which patterns can use an index and which cannot. ## Summary * Use `REGEXP` to match a pattern anywhere in a value. * Anchor with `^` and `$` when the match has to be at the start or the end. * Read `RLIKE` as another name for `REGEXP`. * Use `REGEXP_LIKE(value, pattern, 'c')` to force a case-sensitive match. * Reach for `LIKE` first, because no index can narrow a `REGEXP` search. ## See also * [LIKE](/docs/tutorial/like) — the simpler pattern operator * [Case sensitivity](/docs/tutorial/case-sensitivity) — what decides the default * [WHERE](/docs/tutorial/where) — the clause the pattern goes in * [Full-text search in MySQL](/docs/guides/full-text-search) — searching words in text at scale * [Fuzzy string matching in MySQL](/docs/guides/fuzzy-string-matching) — matching text that is close but not equal