MySQL Regular Expression Queries: Advanced Guide from LIKE to REGEXP

Problem Background and Limitations of the LIKE Operator

Pattern matching is a common requirement in database queries. Users often need to find string records that match specific patterns. MySQL provides the LIKE operator for basic pattern matching, but it has significant limitations when dealing with complex patterns.

Consider the following scenario: the need to query records starting with "ALA" and followed by a single digit. Attempts using the LIKE operator:

SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA[d]%')

and

SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA[0-9]%')

Both queries return empty results, while the simple SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA%') correctly returns records like ALA0000, ALA0001, ALA0002. This indicates that the table indeed contains records starting with ALA followed by digits, but the LIKE operator cannot properly handle character set matching syntax.

Regular Expression Solution

MySQL's REGEXP operator provides full regular expression support, capable of solving complex pattern matching requirements that the LIKE operator cannot handle.

The correct query should be:

SELECT trecord FROM `tbl` WHERE (trecord REGEXP '^ALA[0-9]')

This query utilizes several key features of regular expressions:

• ^: Matches the beginning of the string
• ALA: Exactly matches the character sequence
• [0-9]: Matches any single digit character

Detailed Overview of MySQL Regular Expression Functions

MySQL offers a rich set of regular expression functions to meet various matching needs:

REGEXP/RLIKE Operators

The most basic regular expression matching operators, returning boolean values indicating whether a match occurs:

SELECT 'Michael!' REGEXP '.*';  -- Returns 1
SELECT 'a' REGEXP '^[a-d]';    -- Returns 1

REGEXP_LIKE Function

Functionally equivalent to REGEXP but supports more matching options:

SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE');                    -- Returns 1 (case-insensitive by default)
SELECT REGEXP_LIKE('CamelCase', 'CAMELCASE', 'c');             -- Returns 0 (case-sensitive)
SELECT REGEXP_LIKE('abc', 'ABC', 'c');                         -- Returns 0

REGEXP_INSTR Function

Returns the starting position of the matched substring:

SELECT REGEXP_INSTR('dog cat dog', 'dog');        -- Returns 1
SELECT REGEXP_INSTR('dog cat dog', 'dog', 2);    -- Returns 9 (search starting from position 2)

REGEXP_REPLACE Function

Replaces matched substrings:

SELECT REGEXP_REPLACE('a b c', 'b', 'X');                          -- Returns 'a X c'
SELECT REGEXP_REPLACE('abc def ghi', '[a-z]+', 'X', 1, 3);       -- Returns 'abc def X'

REGEXP_SUBSTR Function

Extracts matched substrings:

SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+');        -- Returns 'abc'
SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+', 1, 3); -- Returns 'ghi'

Detailed Regular Expression Syntax

MySQL regular expressions are implemented based on the ICU (International Components for Unicode) library, supporting full Unicode character sets.

Basic Metacharacters

• ^: Matches the beginning of the string
• $: Matches the end of the string
• .: Matches any single character
• |: Alternation matching (OR operation)

Quantifiers

• *: Matches zero or more times
• +: Matches one or more times
• ?: Matches zero or one time
• {n}: Matches exactly n times
• {n,}: Matches at least n times
• {m,n}: Matches between m and n times

Character Classes

• [abc]: Matches any character among a, b, or c
• [a-z]: Matches any character in the range from a to z
• [^abc]: Matches any character except a, b, or c
• [[:digit:]]: Matches digit characters
• [[:alpha:]]: Matches alphabetic characters

Practical Application Examples

Let's demonstrate the powerful functionality of regular expressions through several practical examples:

Example 1: Email Validation

SELECT email FROM users WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

Example 2: Phone Number Format Validation

SELECT phone FROM contacts WHERE phone REGEXP '^\+?[0-9]{1,3}?[-. ]?[0-9]{1,4}[-. ]?[0-9]{1,4}[-. ]?[0-9]{1,9}$';

Example 3: Extracting URL Domains

SELECT REGEXP_SUBSTR(url, 'https?://([^/]+)') AS domain FROM websites;

Performance Optimization and Best Practices

Although regular expressions are powerful, performance considerations are important in large-scale data queries:

• Avoid using regular expressions at the beginning of WHERE clauses when possible
• Use more specific patterns to reduce the matching scope
• Consider using full-text indexes as alternatives to complex regular expression matching
• Appropriately set the regexp_stack_limit and regexp_time_limit system variables

Character Set and Escape Handling

Character set compatibility must be considered when working with MySQL regular expressions:

-- Binary strings will throw ER_CHARACTER_SET_MISMATCH errors
-- Special characters require double escaping
SELECT REGEXP_LIKE('1+2', '1\\+2');  -- Returns 1

In strings, backslashes require double escaping because the MySQL parser handles one level of escaping first, and then the regular expression engine handles the second level.

Conclusion

MySQL's regular expression functionality provides powerful tools for complex string matching. From the basic REGEXP operator to advanced replacement and extraction functions, developers can choose appropriate tools based on specific requirements. Although regular expressions are more powerful than the LIKE operator, they also require more system resources and deeper understanding. In practical applications, usage should be balanced based on performance requirements and functional needs.

Through the detailed explanations and code examples in this article, developers should be able to master the usage of MySQL regular expressions and effectively solve complex string matching problems in real-world projects.