--- description: What are regular expressions? Regular Expressions help search data matching complex criteria. We looked at wildcards in the previous tutorial. If you have worked with wildcards before, you may title: MySQL Regular Expressions (Regexp) image: https://www.guru99.com/images/mysql-regular-expressions-regexp.png --- [Skip to content](#main) **⚡ Smart Summary** MySQL Regular Expressions (REGEXP) match column values against flexible patterns that wildcards cannot express. This explains REGEXP syntax, the RLIKE synonym, every supported metacharacter, corrected query examples against the myflixdb database, and the regular expression functions added in MySQL 8.0. * 🔍 **Core Operator:** REGEXP compares a column with a pattern and returns 1 for a match, while RLIKE is an exact synonym of REGEXP. * 🧩 **Pattern Anchors:** The caret (^) anchors the match to the start of the value, and the dollar sign ($) anchors it to the end. * 🔤 **Character Lists:** \[abcd\] matches any enclosed character, and \[^abcd\] excludes every enclosed character from the result set. * ➕ **Quantifiers:** The asterisk, plus, and question mark apply to the single character that precedes them, not to the whole string. * 🛡️ **Escaping Rule:** A literal backslash must be doubled inside a pattern, because MySQL processes the string twice. * 🆕 **Version Change:** MySQL 8.0 moved to the ICU engine, replaced \[\[:<:\]\] and \[\[:>:\]\] with \\b, and added REGEXP\_LIKE, REGEXP\_REPLACE, REGEXP\_SUBSTR, and REGEXP\_INSTR. * ⚡ **Performance Note:** REGEXP never uses an index, so filter the rows with an indexed condition first whenever the query allows it. [ Read More ](javascript:void%280%29;) ![]() ## What are MySQL regular expressions? **MySQL regular expressions** help you search for data that matches complex criteria. A regular expression is a pattern that describes the shape of the value you are looking for, rather than the value itself. If you have already worked with [MySQL wildcards](https://www.guru99.com/wildcards.html), you may ask why regular expressions are worth learning when LIKE produces similar results. The answer is expressive power: wildcards offer only two symbols, while regular expressions describe character ranges, alternatives, repetition, and word positions in a single pattern. With the purpose established, the next section introduces the syntax you will use in every REGEXP query. ## Basic syntax of REGEXP The basic syntax for a regular expression is as follows. SELECT * FROM table_name WHERE fieldname REGEXP 'pattern'; **HERE:** * **“SELECT statement”** is the standard [SELECT statement](https://www.guru99.com/select-statement.html). * **“WHERE fieldname”** is the name of the column on which the regular expression is performed. * **“REGEXP ‘pattern'”** — REGEXP is the regular expression operator, and ‘pattern’ represents the pattern to be matched. **RLIKE** is a **synonym for REGEXP** and returns the same results. To avoid confusing it with the LIKE operator, it is better to use REGEXP. Let us now look at a practical example. SELECT * FROM `movies` WHERE `title` REGEXP 'code'; The above query searches for all the movie titles that contain the word “code”. It does not matter whether “code” appears at the beginning, in the middle, or at the end of the title. As long as the title contains the pattern, the row is returned. ### Matching the start of a value with a character list Suppose we want the movies whose titles start with a, b, c, or d, followed by any number of other characters. We combine a character list with the caret metacharacter to achieve that result. SELECT * FROM `movies` WHERE `title` REGEXP '^[abcd]'; Executing the above script in [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) against the myflixdb database gives us the following results. | movie\_id | title | director | year\_released | category\_id | | --------- | -------------------- | ---------- | -------------- | ------------ | | 4 | Code Name Black | Edgar Jimz | 2010 | NULL | | 5 | Daddy’s Little Girls | NULL | 2007 | 8 | | 6 | Angels and Demons | NULL | 2007 | 6 | | 7 | Davinci Code | NULL | 2007 | 6 | In the pattern ‘^\[abcd\]’, the caret (^) requires the match to start at the beginning of the value, and the character list \[abcd\] accepts only titles whose first letter is a, b, c, or d. The comparison is not case sensitive under the default collation, which is why “Code Name Black” is returned. ### Excluding characters with a negated character list Let us now modify the script and negate the character list to see which rows are returned. SELECT * FROM `movies` WHERE `title` REGEXP '^[^abcd]'; Executing the above script in MySQL Workbench against the myflixdb database gives us the following results. | movie\_id | title | director | year\_released | category\_id | | --------- | ------------------------- | ---------------- | -------------- | ------------ | | 1 | Pirates of the Caribean 4 | Rob Marshall | 2011 | 1 | | 2 | Forgetting Sarah Marshal | Nicholas Stoller | 2008 | 2 | | 3 | X-Men | | 2008 | | | 9 | Honey mooners | John Schultz | 2005 | 8 | | 16 | 67% Guilty | | 2012 | | | 17 | The Great Dictator | Chalie Chaplie | 1920 | 7 | | 18 | sample movie | Anonymous | | 8 | | 19 | movie 3 | John Brown | 1920 | 8 | Inside a character list, the caret changes meaning: ‘^\[^abcd\]’ still anchors the match at the beginning, while \[^abcd\] now excludes every title that starts with one of the enclosed characters. These two examples use only anchors and character lists. The next section covers the full metacharacter set. ### RELATED ARTICLES * [MySQL Index: Create, Add & Drop Tutorial ](https://www.guru99.com/indexes.html "MySQL Index: Create, Add & Drop Tutorial") * [MySQL Functions: String, Numeric, User-Defined, Stored ](https://www.guru99.com/functions.html "MySQL Functions: String, Numeric, User-Defined, Stored") * [MySQL Workbench Tutorial: What is, How to Install & Use ](https://www.guru99.com/introduction-to-mysql-workbench.html "MySQL Workbench Tutorial: What is, How to Install & Use") * [SQL Cheat Sheet with Commands & Description (2026) ](https://www.guru99.com/sql-cheat-sheet.html "SQL Cheat Sheet with Commands & Description (2026)") ## Regular expression metacharacters The examples above show the simplest form of a regular expression. Metacharacters allow you to fine-tune a pattern search: they express repetition, alternatives, ranges, and positions. The table below lists every metacharacter supported by the MySQL REGEXP operator, with a corrected example for each one. | Char | Description | Example | | ------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **\*** | The **asterisk (\*)** matches zero (0) or more instances of the **single character** that precedes it. | _SELECT \* FROM movies WHERE title REGEXP ‘da\*’;_ matches a “d” followed by zero or more “a” characters, so Da Vinci Code and Daddy’s Little Girls qualify. Use ‘da+’ when the letter “a” must actually be present. | | **+** | The **plus (+)** matches one or more instances of the character that precedes it. | _SELECT \* FROM \`movies\` WHERE \`title\` REGEXP ‘mon+’;_ gives all movies containing “mon” followed by one or more “n” characters. For example, Angels and Demons. | | **?** | The **question mark (?)** matches zero (0) or one instance of the character that precedes it. | _SELECT \* FROM \`categories\` WHERE \`category\_name\` REGEXP ‘com?’;_ matches “co” with an optional “m”. For example, comedy and romantic comedy. | | **.** | The **dot (.)** matches any single character except a new line. | _SELECT \* FROM movies WHERE \`year\_released\` REGEXP ‘200.’;_ gives all movies released in a year starting with “200” followed by any single character. For example, 2005, 2007, 2008. | | **\[abc\]** | The **character list \[abc\]** matches any one of the enclosed characters. | _SELECT \* FROM \`movies\` WHERE \`title\` REGEXP ‘\[vwxyz\]’;_ gives all movies containing any single character from “vwxyz”. For example, X-Men and Da Vinci Code. | | **\[^abc\]** | The **negated list \[^abc\]** matches any character except the ones enclosed. | _SELECT \* FROM \`movies\` WHERE \`title\` REGEXP ‘^\[^vwxyz\]’;_ gives all movies whose title does not begin with a character in “vwxyz”. | | **\[A-Z\]** | The **range \[A-Z\]** matches any upper case letter. | _SELECT \* FROM \`members\` WHERE \`postal\_address\` REGEXP ‘\[A-Z\]’;_ gives all members whose postal address contains a letter between A and Z. For example, Janet Jones with membership number 1. | | **\[a-z\]** | The **range \[a-z\]** matches any lower case letter. | _SELECT \* FROM \`members\` WHERE \`postal\_address\` REGEXP ‘\[a-z\]’;_ gives all members whose postal address contains a letter between a and z. Note that the default collation is case insensitive, so this range also matches capital letters. | | **\[0-9\]** | The **range \[0-9\]** matches any digit from 0 through 9. | _SELECT \* FROM \`members\` WHERE \`contact\_number\` REGEXP ‘\[0-9\]’;_ gives all members whose contact number contains at least one digit. For example, Robert Phil. | | **^** | The **caret (^)** anchors the match to the beginning of the value. | _SELECT \* FROM \`movies\` WHERE \`title\` REGEXP ‘^\[cd\]’;_ gives all movies whose title starts with “c” or “d”. For example, Code Name Black, Daddy’s Little Girls, and Da Vinci Code. | | **$** | The **dollar sign ($)** anchors the match to the end of the value. | _SELECT \* FROM \`movies\` WHERE \`title\` REGEXP ‘code$’;_ gives all movies whose title ends with “code”. For example, Davinci Code. | | **\|** | The **vertical bar (\|)** isolates alternatives. | _SELECT \* FROM \`movies\` WHERE \`title\` REGEXP ‘^\[cd\]\|^\[u\]’;_ gives all movies whose title starts with “c”, “d”, or “u”. For example, Code Name Black, Da Vinci Code, and Underworld – Awakening. | | **\\b** | The **word boundary (\\b)** matches the start or the end of a word. It replaces the older \[\[:<:\]\] and \[\[:>:\]\] markers, which MySQL 8.0 removed. | _SELECT \* FROM \`movies\` WHERE \`title\` REGEXP ‘\\\\bfor’;_ gives all movies with a word starting with “for”. For example, Forgetting Sarah Marshal. On MySQL 5.7 the equivalent pattern is ‘\[\[:<:\]\]for’. | | **\[\[:class:\]\]** | The **character class** matches a named group of characters: \[\[:alpha:\]\] for letters, \[\[:space:\]\] for white space, \[\[:punct:\]\] for punctuation, and \[\[:upper:\]\] for upper case letters. Note the **double** square brackets. | _SELECT \* FROM \`movies\` WHERE \`title\` REGEXP ‘^\[\[:alpha:\]\[:space:\]\]+$’;_ gives all movies whose title contains letters and spaces only. For example, Forgetting Sarah Marshal, while Pirates of the Caribean 4 is omitted because of the digit. | The backslash (\\) is the escape character. Because MySQL parses the string first and the pattern second, a literal backslash must be written as a double backslash (\\\\) inside a REGEXP pattern. **⚠️ Version warning:** MySQL 8.0.4 replaced the old regular expression engine with the ICU library. The word markers \[\[:<:\]\] and \[\[:>:\]\] were removed in that release, so patterns copied from older material fail with a “syntax error” on MySQL 8.0\. Use \\b instead. Now that every metacharacter is defined, a fair question follows: when should REGEXP replace the simpler LIKE operator? ## REGEXP vs LIKE: which one should you use? Both operators filter rows by pattern, but they solve different problems. LIKE understands two symbols only, while REGEXP understands the full metacharacter set shown above. That power has a cost, so the choice is a trade-off rather than a preference. | Criterion | LIKE | REGEXP | | --------------- | ----------------------------------------------- | ------------------------------------------------------------ | | Pattern symbols | % and \_ only | Anchors, ranges, alternation, quantifiers, character classes | | Typical use | Prefix, suffix, and “contains” searches | Validation, multiple alternatives, position-aware matching | | Index usage | Possible when the pattern does not start with % | Never uses an index | | Return value | TRUE or FALSE | 1 or 0, and NULL when either operand is NULL | Choose LIKE for straightforward matching, because it reads well and can still use an index. Choose REGEXP when a single pattern must express several rules at once, such as “starts with c or d and ends with a digit”. On large tables, narrow the rows first with an indexed condition, then apply REGEXP to that smaller set. ## MySQL 8.0 regular expression functions The REGEXP operator answers only one question: does the value match the pattern? MySQL 8.0 added four functions that go further and let you locate, extract, and rewrite the matching text. Each one accepts an optional match\_type argument, where ‘c’ forces a case-sensitive comparison and ‘i’ forces a case-insensitive one. * **REGEXP\_LIKE(expr, pattern)** returns 1 when the value matches the pattern. It is the function form of the REGEXP operator, and the match\_type argument makes case sensitivity explicit. * **REGEXP\_INSTR(expr, pattern)** returns the position of the first character of the match, or 0 when the pattern is not found. * **REGEXP\_SUBSTR(expr, pattern)** returns the matching substring itself, which is useful for pulling a year, a code, or a number out of a longer text value. * **REGEXP\_REPLACE(expr, pattern, replacement)** returns the value with every match replaced, so it can clean data inside a [SQL UPDATE query](https://www.guru99.com/sql-update-query.html). SELECT title, REGEXP_SUBSTR(title, '[0-9]+') AS number_in_title FROM `movies` WHERE REGEXP_LIKE(title, '[0-9]'); The query above returns every movie title that contains a digit, together with the digits themselves. On MySQL 5.7 these functions are unavailable, so the REGEXP operator remains the only option. ## FAQs 🔠 Is the MySQL REGEXP operator case sensitive? Not by default. REGEXP follows the collation of the column, and the standard collations are case insensitive. Add the BINARY keyword before the column, or call REGEXP\_LIKE with the ‘c’ match type, to force a case-sensitive comparison. 🔁 What is the difference between REGEXP and RLIKE? There is no functional difference. RLIKE is a synonym of REGEXP that MySQL keeps for compatibility. REGEXP is preferred in practice, because RLIKE is easily confused with the unrelated [LIKE operator](https://www.guru99.com/wildcards.html) used for wildcards. 🐢 Why is a REGEXP query slow on a large table? REGEXP cannot use an index, so MySQL evaluates the pattern row by row. Reduce the scanned rows first with an indexed condition, a date range, or a full-text index, and apply the regular expression to that smaller result set. 🤖 Can AI write and explain MySQL regular expressions? Yes. AI assistants convert a plain-language rule into a REGEXP pattern and explain each metacharacter. Test the result in [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) against sample rows, because a single misplaced anchor changes the entire result set. 🧠 Can AI tools clean messy data with regular expressions? Yes. AI assistants generate REGEXP\_REPLACE statements that strip punctuation, normalize phone numbers, or trim stray spaces. Run the pattern inside a SELECT first, confirm the rewritten values, and only then apply it in an [UPDATE statement](https://www.guru99.com/sql-update-query.html). #### Summarize this post with: ChatGPT Perplexity Grok Google AI **Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** ##### Sign up for the newsletter Subscribe for Free You have successfully subscribed. Please check your inbox. ![AI-Newsletter]() Chosen by over **350,000+** professionals [Scroll to top ](#wrapper)Scroll to top × Toggle Menu Close Search for: Search ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/mysql-regular-expressions-regexp.png","url":"https://www.guru99.com/images/mysql-regular-expressions-regexp.png","width":"700","height":"250","caption":"MySQL Regular Expressions (Regexp)","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/regular-expressions.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sql","name":"SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/regular-expressions.html","name":"MySQL Regular Expressions (Regexp)"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/regular-expressions.html#webpage","url":"https://www.guru99.com/regular-expressions.html","name":"MySQL Regular Expressions (Regexp)","dateModified":"2026-07-14T11:39:43+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/mysql-regular-expressions-regexp.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/regular-expressions.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen","description":"I'm Marcus Allen, an SQL and Data Warehousing Consultant with over a decade of experience in designing and optimizing large-scale data solutions.","url":"https://www.guru99.com/author/marcus","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/marcus-allen-author.png","url":"https://www.guru99.com/images/marcus-allen-author.png","caption":"Marcus Allen","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"SQL","headline":"MySQL Regular Expressions (Regexp)","description":"What are regular expressions? Regular Expressions help search data matching complex criteria. We looked at wildcards in the previous tutorial. If you have worked with wildcards before, you may","keywords":"sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen"},"dateModified":"2026-07-14T11:39:43+05:30","image":{"@id":"https://www.guru99.com/images/mysql-regular-expressions-regexp.png"},"copyrightYear":"2026","name":"MySQL Regular Expressions (Regexp)","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"Is the MySQL REGEXP operator case sensitive?","acceptedAnswer":{"@type":"Answer","text":"Not by default. REGEXP follows the collation of the column, and the standard collations are case insensitive. Add the BINARY keyword before the column, or call REGEXP_LIKE with the 'c' match type, to force a case-sensitive comparison."}},{"@type":"Question","name":"What is the difference between REGEXP and RLIKE?","acceptedAnswer":{"@type":"Answer","text":"There is no functional difference. RLIKE is a synonym of REGEXP that MySQL keeps for compatibility. REGEXP is preferred in practice, because RLIKE is easily confused with the unrelated LIKE operator used for wildcards."}},{"@type":"Question","name":"Why is a REGEXP query slow on a large table?","acceptedAnswer":{"@type":"Answer","text":"REGEXP cannot use an index, so MySQL evaluates the pattern row by row. Reduce the scanned rows first with an indexed condition, a date range, or a full-text index, and apply the regular expression to that smaller result set."}},{"@type":"Question","name":"Can AI write and explain MySQL regular expressions?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI assistants convert a plain-language rule into a REGEXP pattern and explain each metacharacter. Test the result in MySQL Workbench against sample rows, because a single misplaced anchor changes the entire result set."}},{"@type":"Question","name":"Can AI tools clean messy data with regular expressions?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI assistants generate REGEXP_REPLACE statements that strip punctuation, normalize phone numbers, or trim stray spaces. Run the pattern inside a SELECT first, confirm the rewritten values, and only then apply it in an UPDATE statement."}}]}],"@id":"https://www.guru99.com/regular-expressions.html#schema-1143636","isPartOf":{"@id":"https://www.guru99.com/regular-expressions.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/regular-expressions.html#webpage"}}]} ```