REGEXP_LIKE

Checks whether a text pattern matches a regular expression string. Returns a BOOLEAN value, specifically false if the text doesn’t match and true if it does match. This is a RE2 regular expression.

Syntax

REGEXP_LIKE(<expression>, '<pattern>'[,'<flag>[...]'])

Parameters

Parameter	Description	Supported input types
`<expression>`	The text searched for a match using the RE2 pattern.	`TEXT`
`<pattern>`	An RE2 regular expression pattern used to search for a match in the `<expression>`. The pattern can be a constant or any `TEXT` expression, including a column reference. Non-constant patterns are supported only when no `<flag>` is specified.	RE2 regular expression
`<flag>`	Optional. Flags allow additional controls over the regular’s expression matching behavior. If using multiple flags, you can include them in the same single-quote block without any separator character. Flags must be constant. To use flags with a non-constant pattern, embed them in the pattern itself, for example `'(?i)' \|\| pattern_column`.	Firebolt supports the following RE2 flags to override default matching behavior. With `-` in front you can disable the flag. * `i` - Specifies case-insensitive matching. * `m` - Specifies multi-line mode. In this mode, `^` and `$` characters in the regex match the beginning and end of line. * `s` - (Enabled per default) Specifies that the `.` metacharacter in regex matches the newline character in addition to any character in `.` * `U` - Specifies non-greedy mode. In this mode, the meaning of the metacharacters `` and `+` in regex `<pattern>` are swapped with `?` and `+?`, respectively. See the examples using flags below for the difference in how results are returned.

Return Type

BOOLEAN

Examples

SELECT REGEXP_LIKE('123', '[a-z]');

regexp_like boolean
False

Rows: 1Execution time: 5.59ms

SELECT REGEXP_LIKE('123', '\d+');

regexp_like boolean
True

Rows: 1Execution time: 15.29ms

Example using a non-constant pattern

The pattern doesn’t have to be a literal. You can match each row against a pattern stored in another column. For example, given a logs table with a message column and a patterns table with a pattern column, return the log messages that match any of the patterns:

SELECT
    l.message, p.pattern
FROM
    logs l
JOIN
    patterns p ON REGEXP_LIKE(l.message, p.pattern);

To apply flags with a non-constant pattern, embed the flag in the pattern itself using the (?flag) syntax. For example, the following query performs case-insensitive matching:

SELECT
    l.message, p.pattern
FROM
    logs l
JOIN
    patterns p ON REGEXP_LIKE(l.message, '(?i)' || p.pattern);

Example using flags

The i flag causes the regular expression to be case-insensitive. Without this flag, this query would return false as no match is found.

SELECT REGEXP_LIKE('ABC', '[a-z]', 'i');

regexp_like boolean
True

Rows: 1Execution time: 7.11ms

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Examples

Example using a non-constant pattern

Example using flags

​Syntax

​Parameters

​Return Type

​Examples

​Example using a non-constant pattern

​Example using flags

Syntax

Parameters

Return Type

Examples

Example using a non-constant pattern

Example using flags