PolarDB:Pattern matching

Escape characters

The default escape character is the backslash (\). Precede a wildcard with the escape character to match it literally. To match the escape character itself, write it twice.

Use the ESCAPE clause to specify a different escape character:

'50%' LIKE '50!%' ESCAPE '!'   -- true

Disable the escape mechanism entirely with ESCAPE ''. This makes it impossible to match a literal underscore or percent sign.

The SQL standard specifies that omitting ESCAPE means there is no escape character (not that it defaults to a backslash), and prohibits zero-length ESCAPE values. PostgreSQL behavior differs slightly from the standard in this regard.

Case-insensitive matching

ILIKE is a case-insensitive variant of LIKE, based on the active locale. It is a PostgreSQL extension not in the SQL standard.

Operator aliases:

These operator names appear in EXPLAIN output because the parser translates LIKE and its variants into these operators internally. LIKE, ILIKE, NOT LIKE, and NOT ILIKE are treated as operators in PostgreSQL syntax and can appear in expression operator ANY (subquery) constructs, although an ESCAPE clause cannot be included in that context.

For simple prefix matching, the ^@ operator and the starts_with function are more direct alternatives.

Extract a substring with SIMILAR TO

The substring function extracts a portion of a string that matches a SQL regular expression pattern. Two calling conventions are supported:

substring(string from pattern for escape-character)
substring(string, pattern, escape-character)

The pattern must match the entire string, or the function returns null. To specify which part of the match to return, include exactly two occurrences of the escape character followed by " in the pattern. The text between these separators is extracted.

The pattern is internally split into three independent regular expressions at the separator positions. The first and third sections match as little text as possible (non-greedy). PostgreSQL also allows one separator (the third regex is taken as empty) or no separators (both the first and third are taken as empty), as an extension to the SQL standard.

Example with #" as the separator:

substring('foobar' from '%#"o_b#"%' for '#')   -- oob
substring('foobar' from '#"o_b#"%' for '#')    -- NULL

Match operators

Examples:

'abc' ~ 'abc'    -- true
'abc' ~ '^a'     -- true, ^ anchors to start of string
'abc' ~ '(b|d)'  -- true, | means OR
'abc' ~ '^(b|c)' -- false

regexp_replace

regexp_replace replaces substrings that match a POSIX pattern:

regexp_replace(source, pattern, replacement [, flags])

In the replacement string, \n (where n is 1-9) inserts the substring matched by the nth parenthesized subexpression. \& inserts the entire matched substring. Use \\ to insert a literal backslash.

Flags: i for case-insensitive matching, g to replace all matches instead of only the first.

regexp_replace('foobarbaz', 'b..', 'X')           -- fooXbaz
regexp_replace('foobarbaz', 'b..', 'X', 'g')      -- fooXX
regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g') -- fooXarYXazY

regexp_match

regexp_match returns a text array of captured substrings from the first match:

regexp_match(string, pattern [, flags])

• No match: returns NULL.

• Match with no parenthesized subexpressions: returns a single-element array containing the whole matched substring.

• Match with parenthesized subexpressions: returns an array where each element is the substring matched by the corresponding subexpression (non-capturing parentheses are not counted).

SELECT regexp_match('foobarbequebaz', 'bar.*que');
-- {barbeque}

SELECT regexp_match('foobarbequebaz', '(bar)(beque)');
-- {bar,beque}

To get just the matched string (not an array), index the result:

SELECT (regexp_match('foobarbequebaz', 'bar.*que'))[1];
-- barbeque

Tip: regexp_match is available in PostgreSQL version 10 and later. For earlier versions, use regexp_matches in a sub-select to get equivalent behavior: ``sql SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab; ``

regexp_matches

regexp_matches has the same syntax as regexp_match but returns a set of text arrays—one row per match. Use the g flag to return all matches:

SELECT regexp_matches('foo', 'not there');
-- (0 rows)

SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
-- {bar,beque}
-- {bazil,barf}

Tip: When you only want the first match, regexp_match is simpler and more efficient than regexp_matches.

regexp_split_to_table and regexp_split_to_array

These functions split a string using a POSIX pattern as a delimiter:

regexp_split_to_table(string, pattern [, flags])  -- returns rows
regexp_split_to_array(string, pattern [, flags])  -- returns a text array

If there is no match to the pattern, the function returns the original string unchanged. Both functions ignore zero-length matches at the start or end of the string, or immediately following a previous match.

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', '\s+') AS foo;
-- the / quick / brown / fox / jumps / over / the / lazy / dog  (9 rows)

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', '\s+');
-- {the,quick,brown,fox,jumps,over,the,lazy,dog}

Extract a substring with POSIX regex

substring with two parameters extracts the first portion of the string that matches a POSIX pattern:

substring(string from pattern)

Returns null if there is no match. If the pattern contains parentheses, returns the portion matched by the first parenthesized subexpression:

substring('foobar' from 'o.b')    -- oob
substring('foobar' from 'o(.)b')  -- o

RE forms

PostgreSQL always assumes ARE (advanced regular expression) rules by default. ERE (extended regular expression, similar to egrep) or BRE (basic regular expression, similar to ed) rules can be chosen by prepending an embedded option to the pattern.

Structure

A regular expression consists of one or more branches separated by |. Each branch is a sequence of quantified atoms or constraints. An atom is matched as a unit; a constraint matches an empty string under specific conditions.

Atoms

An RE cannot end with a backslash.

Quantifiers

The bound values m and n are unsigned decimal integers from 0 to 255 inclusive. Non-greedy quantifiers are available in AREs only.

Constraints

Lookahead and lookbehind constraints cannot contain back references, and all parentheses within them are non-capturing.

Character-entry escapes

Escape values outside the ASCII range (0-127) are encoding-dependent. In UTF-8, they map to Unicode code points (for example, \u1234 means U+1234). An escape value that does not correspond to a valid character in the database encoding never matches any data and raises no error.

Class-shorthand escapes

These shorthands also work inside bracket expressions; for example, [a-c\d] is equivalent to [a-c[:digit:]].

Constraint escapes

Constraint escapes are not valid inside bracket expressions.

Back references

For example, ([bc])\1 matches bb or cc but not bc or cb.

Director prefixes

• ***: — the rest of the RE is an ARE.

• ***= — the rest of the RE is a literal string; all characters are ordinary.

Embedded options

An ARE can start with (?xyz) where xyz is one or more option letters:

Embedded options take effect at the closing ) and can only appear at the start of an ARE (after the ***: director, if any).

Expanded syntax

With the x option, whitespace and #-to-newline comments in the RE are ignored, allowing you to break up and annotate complex expressions. Three exceptions: a \-preceded whitespace or # is retained; whitespace or # inside a bracket expression is retained; whitespace and comments cannot appear inside multi-character symbols like (?:.

Comments can also be written as (?#ttt) (deprecated; use expanded syntax instead).

Differences from XQuery (LIKE_REGEX)

SQL:2008 defines a LIKE_REGEX operator based on the XQuery regular expression standard. PostgreSQL does not yet implement LIKE_REGEX. The regexp_match function provides similar behavior, since XQuery regular expressions are close to the ARE syntax described here.

Key differences between POSIX-based and XQuery regular expressions:

• XQuery character class subtraction ([a-z-[aeiou]]) is not supported.

• XQuery class shorthands \c, \C, \i, \I are not supported.

• XQuery Unicode property classes (\p{UnicodeProperty}, \P{UnicodeProperty}) are not supported.

• POSIX interprets character classes (like \w) by the active locale; XQuery uses Unicode properties.

• XQuery newline handling is broader: it treats \r, \r\n, and certain Unicode characters (e.g., LINE SEPARATOR U+2028) as newlines in addition to \n. Notably, . and \s count \r\n as one character, not two, according to SQL.

• XQuery character-entry escapes: only \n, \r, and \t are supported.

• XQuery does not support [::] character class syntax in bracket expressions.

• XQuery has no lookahead, lookbehind, constraint escapes, or metasyntax forms.

• XQuery flag letters differ from POSIX: while the i and q options behave the same, others do not. XQuery's s (dot matches newline) and m (^ and $ match at newlines) differ from POSIX s and m; XQuery's x (ignore whitespace) differs from POSIX expanded mode in that POSIX also allows # comments and preserves backslash-escaped whitespace.