Strings & regular expressions

These functions operate on VARCHAR values. Function names are case-insensitive.

Function	Result
contains	Whether text contains a substring.
length	Character count.
substring	A character slice.
regexp_full_match	Whether the entire string matches a pattern.
regexp_replace	Text with the first match replaced.
regexp_jit_replace	First-match replacement using PCRE2 JIT.

contains

contains(string, substring)

Returns a boolean indicating whether the string column contains the constant substring.

SELECT contains(text, 'ivo') AS matches
FROM (VALUES ('Pivot'), ('Lake')) AS input(text);
-- matches: true, false

length

length(string)

Returns the number of characters in the string. len and strlen are aliases.

SELECT length(text) AS characters
FROM (VALUES ('Pivot')) AS input(text);
-- characters: 5

substring

substring(string, start[, length])

Returns a string slice, counting positions in characters. substr is an alias. start and length must be non-negative integer constants. Omitting length reads to the end of the string.

Positions start at 1. A start of 0 is also accepted: with an explicit length, it consumes one character of that length before the beginning of the string. Negative positions and lengths are not supported.

SELECT substring(text, 2, 3) AS piece
FROM (VALUES ('Pivot')) AS input(text);
-- piece: ivo

regexp_full_match

regexp_full_match(string, pattern)

Returns a boolean indicating whether the complete string matches the constant pattern. The ~, !~, and SIMILAR TO forms use the same matcher.

SELECT regexp_full_match(text, '[a-z]+') AS matches
FROM (VALUES ('pivot'), ('pivot42')) AS input(text);
-- matches: true, false

The optional regex settings argument is not supported.

regexp_replace

regexp_replace(string, pattern, replacement)

Returns text with the first matching substring replaced. Both pattern and replacement must be constants.

SELECT regexp_replace(text, 'a', 'X') AS replaced
FROM (VALUES ('banana')) AS input(text);
-- replaced: bXnana

A fourth settings argument is not supported.

regexp_jit_replace

regexp_jit_replace(string, pattern, replacement)

Returns text with the first match replaced, using a pattern compiled by PCRE2 JIT. Pattern and replacement must be constants.

SELECT regexp_jit_replace(text, 'a', 'X') AS replaced
FROM (VALUES ('banana')) AS input(text);
-- replaced: bXnana