How to use the MySQL REGEXP() function

In this article, we will learn how to use the MySQL REGEXP() function, which performs a pattern match of a string expression against a regular expression. We will also see some examples of how to use this function in different situations, and explore some related functions that can be helpful for working with strings and regular expressions.

Syntax

The syntax of the REGEXP() function is as follows:

string REGEXP pattern

The string parameter can be any string expression. The pattern parameter can be any regular expression. The REGEXP() function returns 1 if the string matches the pattern, 0 if the string does not match the pattern, or NULL if either parameter is NULL. The REGEXP() function is case-insensitive by default, unless the BINARY keyword is used before the pattern. The REGEXP() function supports a subset of the Perl-compatible regular expressions (PCRE) syntax, which can be found in the MySQL documentation.

Examples

Let’s see some examples of how to use the REGEXP() function in MySQL.

Example 1: Check if a string contains a substring

We can use the REGEXP() function to check if a string contains a substring. For example:

SELECT 'Hello World' REGEXP 'World' AS result;

This query will check if the string ‘Hello World’ contains the substring ‘World’. The query will return 1, since the string matches the pattern.

Example 2: Check if a string starts with a substring

We can use the REGEXP() function with the ^ anchor, which matches the beginning of the string, to check if a string starts with a substring. For example:

SELECT 'Hello World' REGEXP '^Hello' AS result;

This query will check if the string ‘Hello World’ starts with the substring ‘Hello’. The query will return 1, since the string matches the pattern.

Example 3: Check if a string ends with a substring

We can use the REGEXP() function with the $ anchor, which matches the end of the string, to check if a string ends with a substring. For example:

SELECT 'Hello World' REGEXP 'World$' AS result;

This query will check if the string ‘Hello World’ ends with the substring ‘World’. The query will return 1, since the string matches the pattern.

Example 4: Check if a string matches a pattern with wildcards

We can use the REGEXP() function with the . and * metacharacters, which match any single character and zero or more occurrences of the preceding character, respectively, to check if a string matches a pattern with wildcards. For example:

SELECT 'Hello World' REGEXP 'H.*o' AS result;

This query will check if the string ‘Hello World’ matches the pattern ‘H.*o’, which means any string that starts with ‘H’ and ends with ‘o’. The query will return 1, since the string matches the pattern.

Example 5: Check if a string matches a pattern with character classes

We can use the REGEXP() function with the [ and ] metacharacters, which define a character class that matches any one of the characters within the brackets, to check if a string matches a pattern with character classes. For example:

SELECT 'Hello World' REGEXP '[aeiou]' AS result;

This query will check if the string ‘Hello World’ matches the pattern ‘[aeiou]’, which means any string that contains a vowel. The query will return 1, since the string matches the pattern.

There are some other functions that are related to the REGEXP() function, and can be useful for working with strings and regular expressions. Here are some of them:

LIKE(): This function performs a simple pattern match of a string expression against a pattern that can contain the % and _ wildcards. For example, LIKE('Hello World', 'H%o') returns 1.
RLIKE(): This function is a synonym for the REGEXP() function. For example, RLIKE('Hello World', 'H.*o') returns 1.
REGEXP_INSTR(): This function returns the position of the first occurrence of a regular expression pattern in a string. For example, REGEXP_INSTR('Hello World', 'o') returns 5.
REGEXP_REPLACE(): This function replaces all occurrences of a regular expression pattern in a string with a replacement string. For example, REGEXP_REPLACE('Hello World', 'o', 'a') returns ‘Hella Warld’.
REGEXP_SUBSTR(): This function returns the substring that matches a regular expression pattern in a string. For example, REGEXP_SUBSTR('Hello World', 'o.*o') returns ‘ollo Wo’.

Conclusion

In this article, we learned how to use the MySQL REGEXP() function, which performs a pattern match of a string expression against a regular expression. We also saw some examples of how to use this function in different situations, and explored some related functions that can be helpful for working with strings and regular expressions.