IBM® Informix® 12.10
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 lvarcharregex_split(
str clob,
re lvarchar,
limit integer DEFAULT 0,
copts integer DEFAULT 1)
returns lvarcharParameters
- 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
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]*ickThe 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.