Skip to main content

Categories: String, Regular Expressions

REGEXP_SPLIT

Splits an input string by using a regular expression according to a keyword and an integer value.

Syntax​

REGEXP_SPLIT(input string, regex string, keyword string, integer integer) → array​

  • input: The string that you want to split by means of the regular expression.
  • regex: The regular expression to use to split the string.
  • keyword: The keyword that determines where or how many times to use the regular expression to split the string. Can be FIRST, LAST, INDEX, or ALL.
  • integer: The value specified for the keyword.

Examples

REGEXP_SPLIT example 
SELECT REGEXP_SPLIT('REGULAR AIR', 'R', 'FIRST', -1) AS R_LESS_SHIPMENT_TYPE
-- ['', 'EGULAR AIR']
REGEXP_SPLIT example 
SELECT REGEXP_SPLIT('REGULAR AIR', 'R', 'LAST', -1) AS R_LESS_SHIPMENT_TYPE
-- ['REGULAR AI', '']
REGEXP_SPLIT example 
SELECT REGEXP_SPLIT('REGULAR AIR', 'R', 'INDEX', 1) AS R_LESS_SHIPMENT_TYPE
-- ['REGULA', ' AIR']
REGEXP_SPLIT example 
SELECT REGEXP_SPLIT('REGULAR AIR', 'R', 'ALL', 1) AS R_LESS_SHIPMENT_TYPE
-- ['']
REGEXP_SPLIT example 
SELECT REGEXP_SPLIT('REGULAR AIR', 'R', 'ALL', 2) AS R_LESS_SHIPMENT_TYPE
-- ['', 'EGULA']
REGEXP_SPLIT example 
SELECT REGEXP_SPLIT('REGULAR AIR', 'R', 'ALL', 3) AS R_LESS_SHIPMENT_TYPE
-- ['', 'EGULA', ' AI']
REGEXP_SPLIT example 
SELECT REGEXP_SPLIT('REGULAR AIR', 'R', 'ALL', 4) AS R_LESS_SHIPMENT_TYPE
-- ['', 'EGULA', ' AI', '']

Usage Notes​

You can choose from these keywords:

FIRST
Specifies to split the string at the first match of the regular expression. The integer value is not used and must always be -1.
LAST
Specifies to split the string at the last match of the regular expression. The integer value is not used and must always be -1.
INDEX
Specifies to start finding matches for the regular expression at the specified character position (indicated by the integer value) in the string. Then, split the string at every subsequent match of the regular expression.
ALL
Specifies to split the string as many times as indicated by the integer value. All subsequent matches of the regular expression are ignored.