regex_split function

The regex_split function splits a string into substrings, using the match character as the delimiter.

Syntax

regex_split(
     str     lvarchar,
     re      lvarchar,
     limit   integer DEFAULT 0,
     copts   integer DEFAULT 1)   
returns lvarchar
regex_split(
     str     clob,
     re      lvarchar,
     limit     integer DEFAULT 0,
     copts_string   lvarchar)
returns lvarchar

Parameters

str
The string to search. Can be of type CHAR, NCHAR, VARCHAR, NVARCHAR, LVARCHAR, or CLOB. A null value is treated as an empty string.
re
The regular expression. Can be of type CHAR, NCHAR, VARCHAR, NVARCHAR, or LVARCHAR. Cannot be null.
limit (Optional)
0 = Default. All matches are returned.
Positive integer = The maximum number of matches to return.
copts (Optional)
The type of regex search:
  • 0 = Basic regex
  • 1 = Default. Extended POSIX regex
  • 2 = Basic regex and ignore case
  • 3 = Extended POSIX regex and ignore case
copts_string (Optional)
The type of regex search:
  • basic = Basic regex
  • extended = Default. Extended POSIX regex
  • basic,icase = Basic regex and ignore case
  • extended,icase = Extended POSIX regex and ignore case
  • basic,rtrim = Basic regex with rtrim
  • extended,rtrim = Extended POSIX regex with rtrim
  • basic,icase,rtrim = Basic regex and ignore case with rtrim
  • extended,icase,rtrim = Extended POSIX regex and ignore case with rtrim

Description

Use the regex_split function to split a string into substrings.

The regex_split function and the regex_extract function perform the complete opposite actions of each other.

Returns

A set of text values.

No rows found = The delimiter specified in the regular expression matches the entire source string.

An exception = An error occurred.

Example: Compare the regex_extract and regex_split functions

You are looking for the pattern "ick" and any characters that precede it:
( |^)[A-Za-z]*ick
The regex_extract function returns each substring that matches the regular expression:
execute function
     regex_extract(
     'Jack be nimble, Jack be quick, Jack jump over the candlestick.',
     '( |^)[A-Za-z]*ick'
);
(expression) quick
(expression) candlestick
2 row(s) retrieved.
The regex_split function splits the string into substrings, using the regular expression as the delimiter:
execute function
     regex_split(
     'Jack be nimble, Jack be quick, Jack jump over the candlestick.',
     '( |^)[A-Za-z]*ick');
(expression) Jack be nimble, Jack be
(expression) , Jack jump over the
(expression) .
3 row(s) retrieved.

Example: Split a string into separate words

The following example splits the string up into its separate words, using a space as the delimiter:
execute function
     regex_split(
     'Jack be nimble, Jack be quick, Jack jump over the candlestick.',
     ' ');
(expression) Jack
(expression) be
(expression) nimble,
(expression) Jack
(expression) be
(expression) quick,
(expression) Jack
(expression) jump
(expression) over
(expression) the
(expression) candlestick.
11 row(s) retrieved.
The following example limits the number of substrings into which the source string is split to 5:
execute function
     regex_split(
     'Jack be nimble, Jack be quick, Jack jump over the candlestick.',
     ' ',
     5);
(expression) Jack
(expression) be
(expression) nimble,
(expression) Jack
(expression) be quick, Jack jump over the candlestick.
5 row(s) retrieved.

Example: The regular expression matches the entire source string

In the following example, the delimiter specified in the regular expression matches the entire source string and returns "No rows found.":
execute function regex_split('Hello world', 'Hello world');
No rows found.