Return true when the input string contains a match for the regular expression.
REGEXP_LIKE(str, pattern [, flags])## Overview Returns true when the input string contains at least one substring that matches the regular expression, and false otherwise. By default, the pattern is searched anywhere in the string; to require a full-string match, anchor the pattern with `^` and `$`. REGEXP_LIKE is the regex counterpart of the LIKE operator. It is the natural choice when the pattern you want to match is too complex for LIKE's `%` / `_` wildcards, when you need character classes, or when you want case-insensitive matching without pre-lowercasing both sides. ## Behavior - Returns NULL when either argument is NULL. - Returns true on any successful match anywhere in the string. - Returns false on no match. - The pattern is not anchored by default; use `^` and `$` for full-string match. - Operates on Unicode code points. - Pattern compilation errors raise an error at query time. ## Regex flavor - Rust regex crate, Perl-compatible for most common features. - Supports anchors, character classes, alternation, quantifiers, capturing groups, Unicode classes (`\p{L}`, `\p{N}`, etc.), and escape sequences (`\d`, `\w`, `\s` and their uppercase negated forms). - Does NOT support backreferences in the pattern. - Does NOT support lookahead or lookbehind. - Flags: 'i' case-insensitive, 'c' case-sensitive (default), 'n' or 's' dot-matches-newline, 'm' multi-line. ## Compatibility - Matches the common SQL REGEXP_LIKE semantics. - Semantically equivalent to str ~ pattern (case-sensitive) and str ~* pattern (case-insensitive) when those operator forms are supported.
| Name | Type | Description |
|---|---|---|
str | Specifies the input string to test against the pattern. | |
pattern | Specifies the regular expression pattern. Matches are searched anywhere in the string by default; anchor with ^ and $ for full-string match. | |
flags | Specifies match flags: 'i' (case-insensitive), 'c' (case-sensitive, default), 'n'/'s' (dot matches newline), 'm' (multi-line mode). |
-- Pattern match anywhere in the string
SELECT REGEXP_LIKE('hello123', '[0-9]+'); -- true-- No match
SELECT REGEXP_LIKE('hello', '^[0-9]+$'); -- false-- Case-insensitive match
SELECT REGEXP_LIKE('Hello', 'hello', 'i'); -- true-- Email-format validation
SELECT REGEXP_LIKE('user@example.com', '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'); -- true-- Filter rows matching a pattern
SELECT val
FROM (VALUES ('abc'), ('123'), ('a1b2')) AS t(val)
WHERE REGEXP_LIKE(val, '^[a-z]+$');-- NULL propagates
SELECT REGEXP_LIKE(CAST(NULL AS VARCHAR), 'x'); -- NULL