REGEXP_SPLIT_TO_ARRAY

Splits a string using a regular expression pattern and returns the segments as an array.

Syntax

REGEXP_SPLIT_TO_ARRAY(string, pattern [, flags text])

Supported flags Parameter:

Provides flexible regular expression configuration options, controlling matching behavior by combining the following characters:

• i (case-insensitive): Pattern matching ignores case.
• c (case-sensitive): Pattern matching is case-sensitive (default behavior).
• n or m (multi-line): Enables multi-line mode. In this mode, ^ and $ match the beginning and end of the string, respectively, as well as the beginning and end of each line; the dot . does not match newline characters.
• s (single-line): Enables single-line mode (also known as dot-matches-newline). In this mode, the dot . matches any character, including newline characters.
• x (ignore-whitespace): Ignores whitespace characters in the pattern (improves pattern readability).
• q (literal): Treats the pattern as a literal string rather than a regular expression.

Examples

Basic Splitting

SELECT REGEXP_SPLIT_TO_ARRAY('apple,orange,banana', ',');
┌───────────────────────────────────────────┐
│ ["apple","orange","banana"]               │
└───────────────────────────────────────────┘

Complex Delimiters

SELECT REGEXP_SPLIT_TO_ARRAY('2023-01-01T14:30:00', '[-T:]');
┌───────────────────────────────────────────────────────┐
│ ["2023","01","01","14","30","00"]                     │
└───────────────────────────────────────────────────────┘

Handling Empty Elements

SELECT REGEXP_SPLIT_TO_ARRAY('a,,b,,,c', ',+');
┌───────────────────────────────────┐
│ ["a","b","c"]                     │
└───────────────────────────────────┘

With flag text

SELECT regexp_split_to_array('One_Two_Three', '[_-]', 'i')

╭─────────────────────────────────────────────────────╮
│ ['One','Two','Three']                               │
╰─────────────────────────────────────────────────────╯

See Also

• SPLIT: For simple string splitting
• REGEXP_SPLIT_TO_TABLE: split string to table