# String Functions (Regular Expressions)¶

These string functions perform operations that match a regular expression (often referred to as a “regex”).

In this Topic:

## List of Regex Functions¶

Function

Notes

REGEXP

Alias for RLIKE.

REGEXP_COUNT

REGEXP_EXTRACT_ALL

Alias for REGEXP_SUBSTR_ALL.

REGEXP_INSTR

REGEXP_LIKE

Alias for RLIKE.

REGEXP_REPLACE

REGEXP_SUBSTR

REGEXP_SUBSTR_ALL

RLIKE

## General Usage Notes¶

In these notes, “subject” refers to the string to operate on and “pattern” refers to the regular expression:

• The subject is typically a variable column, while the pattern is typically a constant, but this is not required; every argument to a regular expression function can be either a constant or variable.

• Patterns support the full POSIX ERE (Extended Regular Expression) syntax. For details, see the POSIX basic and extended section (in Wikipedia).

• Patterns also support the following Perl backslash-sequences:

• \d: decimal digit (0-9).

• \D: not a decimal digit.

• \s: whitespace character.

• \S: not a whitespace character.

• \w: “word” character (a-z, A-Z, underscore (“_”), or decimal digit).

• \W: not a word character.

• \b: word boundary.

• \B: not a word boundary.

For details, see the Character classes section (in Wikipedia) or the Backslash sequences section (in the Perl documentation).

Note

In single-quoted string constants, you must escape the backslash character in the backslash-sequence. For example, to specify \d, use \\d. For details, see Specifying Regular Expressions in Single-Quoted String Constants (in this topic).

FROM wildcards
ORDER BY w2;


## Specifying Regular Expressions in Single-Quoted String Constants¶

If you are using a regular expression in a single-quoted string constant, you must escape any backslashes in backslash-sequences with a second backslash.

Note

To avoid escaping backslashes in a regular expression, you can use a dollar-quoted string constant, rather than a single-quoted string constant.

For example:

### Example of Using Metacharacters in a Single-Quoted String Constant¶

This example uses the backslash as part of an escape sequence in a regular expression that searches for a question mark (?).

Create a table and insert a row that contains a single backslash in one column and a question mark in another column:

create table wildcards (w varchar, w2 varchar);
insert into wildcards (w, w2) values ('\\', '?');


The following SELECT searches for the question mark literal. The search uses a regular expression, and the question mark is a meta-character in regular expressions, so the search must escape the question mark to treat it as a literal. Because the backslash appears in a string literal, the backslash itself must also be escaped, so the query looks like:

select w2
from wildcards
where regexp_like(w2, '\\?');
+----+
| W2 |
|----|
| ?  |
+----+


The following query makes it easier to see that the regular expression is composed of two characters (the backslash escape character and the question mark):

select w2
from wildcards
where regexp_like(w2, '\\' || '?');
+----+
| W2 |
|----|
| ?  |
+----+


In the previous example, the extra backslash was needed only because the escape character was part of a string literal, not for the regular expression itself. The following SELECT statement does not need to parse a string literal as part of the SQL command string, and therefore does not need the extra escape character that the string literal needed:

select w, w2, w || w2 as escape_sequence, w2
from wildcards
where regexp_like(w2, w || w2);
+---+----+-----------------+----+
| W | W2 | ESCAPE_SEQUENCE | W2 |
|---+----+-----------------+----|
| \ | ?  | \?              | ?  |
+---+----+-----------------+----+


### Example of Using Backreferences in a Single-Quoted String Constant¶

If you use a backreference (e.g. \1) in a string literal, you must escape the backslash that is a part of that backreference. For example, to specify the backreference \1 in a replacement string literal of REGEXP_REPLACE, use \\1.

The following example uses the table created earlier. The SELECT uses a backreference to replace each occurrence of the regular expression .old with a copy of the matched string preceded by the word “very”:

insert into wildcards (w, w2) values (NULL, 'When I am cold, I am bold.');

select w2, regexp_replace(w2, '(.old)', 'very \\1')
from wildcards
order by w2;
+----------------------------+------------------------------------------+
| W2                         | REGEXP_REPLACE(W2, '(.OLD)', 'VERY \\1') |
|----------------------------+------------------------------------------|
| ?                          | ?                                        |
| When I am cold, I am bold. | When I am very cold, I am very bold.     |
+----------------------------+------------------------------------------+