MySQL regexp_substr() Function

End Tutorial

The regexp_substr() returns the substring of the expression that matches the pattern. The basic syntax is

REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]];

Here the match_type options are the same as the regexp_instr() or regexp_replace() functions.

Examples

1. Basic usage

In this example, we are simply checking if the string "do we not" is present in the input string or not.

select REGEXP_SUBSTR('If you prick us, do we not bleed? If you tickle us, do we not laugh? If you poison us, do we not die? And if you wrong us, shall we not revenge?','do we not');

2. Regexp_substr with offset and occurrence

Here we start searching the input string at the index 5 and then check for the second occurrence. So only the string "ghi" is considered and since it is all characters, it is returned.

SELECT REGEXP_SUBSTR('abc def ghi', '[a-z]+',5,2);

3. Regexp_substr substring with regex

Using a regex pattern we can get a big chunk of the substring out as follows:

select REGEXP_SUBSTR('If you prick us, do we not bleed? If you tickle us, do we not laugh? If you poison us, do we not die? And if you wrong us, shall we not revenge?','p.* .s,');