Master MySQL Regex: All REGEXP Functions Explained

MySQL implements regular‑expression matching through the ICU library, providing full Unicode support and multibyte safety; versions prior to 8.0.4 used the Henry Spencer library, where REGEXP and RLIKE operated on bytes and could produce unexpected results with multibyte character sets.

REGEXP and NOT REGEXP

The expr REGEXP pat operator returns 1 when expr matches the pattern pat, otherwise 0; NULL is returned if either argument is NULL. Its inverse is expressed as expr NOT REGEXP pat, equivalent to NOT(expr REGEXP pat).

REGEXP_LIKE

Syntax: REGEXP_LIKE(expr, pat[, match_type]). It returns 1 for a match, 0 otherwise, and NULL if any argument is NULL. The optional match_type string can contain: c: case‑sensitive matching. i: case‑insensitive matching. m: multiline mode, where ^ and $ recognize line terminators. n: the dot . matches line terminators. u: Unix‑style line endings only.

When contradictory flags are supplied, the rightmost character takes precedence. If a binary string is used, the i flag is ignored and matching becomes case‑sensitive. To match a literal backslash, write \\ unless the NO_BACKSLASH_ESCAPES SQL mode is enabled.

REGEXP_REPLACE

Syntax:

REGEXP_REPLACE(expr, pat, repl[, pos[, occurrence[, match_type]]])

. It replaces substrings of expr that match pat with repl and returns the result. Parameters: pos: starting position for the search (default 1). occurrence: which match to replace (0 means replace all, default 0). match_type: same flag semantics as in REGEXP_LIKE.

Example: replace all digits from the second character onward with asterisks.

REGEXP_SUBSTR

Syntax: REGEXP_SUBSTR(expr, pat[, pos[, occurrence[, match_type]]]). Returns the substring of expr that matches pat, or NULL if no match. Parameters: pos: start position (default 1). occurrence: which match to return (default 1). match_type: same as in REGEXP_LIKE.

REGEXP_INSTR

Syntax:

REGEXP_INSTR(expr, pat[, pos[, occurrence[, return_option[, match_type]]]])

. Returns the 1‑based position of the substring that matches pat, or 0 if no match. Parameters: pos: start position (default 1). occurrence: which match to locate (default 1). return_option: 0 returns the start position of the match, 1 returns the position after the match (default 0). match_type: same flag set as in REGEXP_LIKE.

RLIKE and NOT RLIKE

The RLIKE operator is synonymous with REGEXP, and NOT RLIKE mirrors NOT REGEXP. They share the same syntax and behavior, allowing interchangeable use.

All functions illustrated above include example queries (shown as images in the original source) that demonstrate typical usage patterns such as pattern matching, case handling, multiline searches, and replacement operations.