SQL Reference - REGEXP

REGEXP(expr, pat)

expr: This is the string to be processed. REGEXP() function applies the pattern to this string.
pat: This denotes the pattern that should be used for matching against the given string. Regular expressions in MySQL are supported through this parameter.

Example

SELECT * FROM employees
WHERE name REGEXP '^[aeiou].*[aeiou]$';

Output

+---------+-------+
| Emp_ID  | name  |
+---------+-------+
|    1    | Alex  |
|    4    | Otto  |
+---------+-------+

Explanation

The REGEXP pattern ’^[aeiou].*[aeiou]$’ matches any name entries in the ‘employees’ table that start and end with a vowel. It returned ‘Alex’ and ‘Otto’ as they meet this criterion.

REGEXP_MATCHES(string, pattern [, flags]) RETURNS SETOF text[]

string: This is the string to be checked against the defined pattern.
pattern: This pattern is used for comparison with the string. It is defined by the user.
flags: Optional parameter. These flags modify the function behavior. For instance, ‘i’ for case insensitive matching.

Example

SELECT release_year FROM movies WHERE title ~* '.*war.*';

Output

 release_year
--------------
 1977
 1980
 1983
 1999
 2002
 2005
 2015
 2017
 2019
(9 rows)

Explanation

The example uses REGEXP, represented by the ’~*’ operator, to search for the string ‘war’ in a case-insensitive manner within the ‘title’ columns of the ‘movies’ table. The query retrieves corresponding ‘release_year’ of the matched titles.

REGEXP(pattern IN CLOB, string IN VARCHAR2, position IN PLS_INTEGER DEFAULT 1, occurrence IN PLS_INTEGER DEFAULT 1, match_parameter IN VARCHAR2 DEFAULT NULL) RETURN PLS_INTEGER;

pattern in clob: This parameter is a character large object (CLOB) that represents the pattern to be searched for in the input string.
string in varchar2: This parameter is the string where the pattern, specified in the CLOB parameter, will be searched for.
position in pls_integer default 1: This optional parameter defines from which character position in the string the function should begin searching for the pattern. It is set to 1 by default, indicating the function should search from the first character of the string.
occurrence in pls_integer default 1: This optional parameter indicates which occurrence of the pattern in the string should be returned. By default, it is set to 1, meaning the function will return the first occurrence of the pattern.
match_parameter in varchar2 default null: This optional parameter is a set of characters that define the behavior of the function. For example, ‘i’ for case-insensitive matching, ‘c’ for case-sensitive matching, ‘n’ to allow matching any character including newline. Default is NULL which indicates a case sensitive, single line search.

Example

SELECT name
FROM employees
WHERE name REGEXP '^[A-D]'

Output

name
1  Alice
2  Bob
3  Charles

Explanation

The above SQL query selects the names of employees whose names begin with a letter from A to D, inclusive. The REGEXP operator is used with the pattern '^[A-D]', which matches any string that starts with any of the letters A, B, C, or D.

REGEXP(pattern, input_string)

pattern: This is the regular expression pattern that the function will attempt to match within the input_string. This is a string value which is parsed according to the Perl compatible regular expression language.
input_string: This is the string against which the pattern regular expression is matched. It is searched for instances that match the pattern. This is typically field data within an SQL table but may also be string literal.

Example

SELECT name FROM employees WHERE name REGEXP '[a-e]';

Output

Ada
Bob
Dave

Explanation

The SQL command is selecting the names from the employees table where the names are a match to the regular expression [a-e]. This regular expression would match all names starting with letters between ‘a’ and ‘e’. The output presents the matches found: ‘Ada’, ‘Bob’, and ‘Dave’.