REGEXP_SPLIT_TO_ARRAY

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Splits a string into an array using a regular expression pattern as the delimiter.

1regexp_split_to_array(<string>, <pattern> [, flags])

• <string>: The input text string to split.
• <pattern>: The regular expression pattern to use as the delimiter.

• flags: A string of option flags that modify matching behavior. Flags can be combined (for example, 'iq').

Returns an array of text values containing all substrings after splitting the input string using the pattern as a delimiter.

• Empty input string: Returns an array with a single empty string [""].
• Empty pattern: Splits the string into individual characters, including Unicode characters.
• Boundary matches: When the pattern matches at the start or end of the string, empty strings are included in the result array.
• Consecutive matches: Empty strings appear between consecutive delimiter matches.
• Invalid patterns: Throws an error for invalid regex patterns (for example, unsupported lookbehind assertions, invalid quantifiers like .*+).
• Unicode support: Handles multi-byte Unicode characters correctly.
• For simple delimiters without regex features, consider using SPLIT instead.
• For detailed regex syntax, see Regular Expression Syntax.

Split a string on one or more whitespace characters.

1SELECT regexp_split_to_array('First Last', '\s') AS result;

Returns array['First', 'Last'].

Split using a character class to match multiple delimiters.

1SELECT regexp_split_to_array('abc123XYZ', '[0-9]') AS result;

Returns array['abc', '', '', 'XYZ']. Note the empty strings between consecutive digit matches.

1SELECT regexp_split_to_array('abc123XYZ', '[0-9]+') AS result;

Returns array['abc', 'XYZ']. Note there are no empty strings between consecutive digit matches, because the digits were matched as a group.

An empty pattern splits the string into individual characters.

1SELECT regexp_split_to_array('abc', '') AS result;

Returns array['a', 'b', 'c'].

1SELECT regexp_split_to_array('aéé', '') AS result;

Returns array['a', 'é', 'é']. Unicode characters are handled correctly.

When the pattern matches at the start or end, empty strings are included.

1SELECT regexp_split_to_array('abab', 'ab') AS result;

Returns array['', '', '']. The pattern matches twice, creating three segments (all empty).

Use the i flag for case-insensitive delimiter matching.

1SELECT regexp_split_to_array('aBAb', 'ab', 'i') AS result;

Returns array['', '', ''].

Use the q flag to treat the pattern as a literal string, disabling regex metacharacters.

1SELECT regexp_split_to_array('\d', '\d', 'q') AS result;

Returns array['', '']. Without the q flag, \d would match any digit.

Combine flags for case-insensitive literal matching.

1SELECT regexp_split_to_array('acAc?', 'acac?', 'iq') AS result;

Returns array['', ''].

Use word boundary anchors for precise matching.

1SELECT regexp_split_to_array('cats and dogs', '\bcats\b') AS result;

Returns array['', ' and dogs'].

1SELECT regexp_split_to_array('cats at the flat', '\bat\b') AS result;

Returns array['cats ', ' the flat']. Matches only the standalone word “at”.

Escape regex metacharacters to match them literally.

1SELECT regexp_split_to_array('file.txt', 'file\.txt') AS result;

Returns array['', ''].

1SELECT regexp_split_to_array('apples+oranges', 'apples\+oranges') AS result;

Returns array['', ''].

• SPLIT
• SPLIT_PART
• REGEXP_SUBSTR
• Regular Expression Syntax