Categories:

String & Binary Functions (General)

SPLIT

Splits a given string with a given separator and returns the result in an array of strings.

Contiguous split strings in the source string, or the presence of a split string at the beginning or end of the source string, results in an empty string in the output. An empty separator string results in an array containing only the source string. If either parameter is a NULL, a NULL is returned.

The result can be used with functions and constructs operating on semi-structured data, e.g. FLATTEN, ARRAY_SIZE.

See also

SPLIT_PART

Syntax

SPLIT(<string>, <separator>)

Arguments

string

Text to be split into parts.

separator

Text to split string by.

Collation Details

Arguments with collation specifications are currently not supported.

Examples

Split the localhost IP address 127.0.0.1 into an array consisting of each of the four parts:

SELECT SPLIT('127.0.0.1', '.');

+-------------------------+
| SPLIT('127.0.0.1', '.') |
|-------------------------|
| [                       |
|   "127",                |
|   "0",                  |
|   "0",                  |
|   "1"                   |
| ]                       |
+-------------------------+

Split a string that contains vertical lines as separators (note that the output will contain empty strings):

SELECT SPLIT('|a||', '|');

+--------------------+
| SPLIT('|A||', '|') |
|--------------------|
| [                  |
|   "",              |
|   "a",             |
|   "",              |
|   ""               |
| ]                  |
+--------------------+

Use the result of SPLIT to generate multiple records from a single string using the LATERAL FLATTEN construct. FLATTEN is a table function that takes a VARIANT, OBJECT, or ARRAY column and produces a lateral view, i.e. an inline view that contains correlation referring to other tables that precede it in the FROM clause:

SELECT * FROM persons;

------+---------------------+
 NAME |      CHILDREN       |
------+---------------------+
 Mark | Marky,Mark Jr,Maria |
 John | Johnny,Jane         |
------+---------------------+

SELECT name, C.value::string AS childName
FROM persons,
     LATERAL FLATTEN(input=>split(children, ',')) C;

------+-----------+
 NAME | CHILDNAME |
------+-----------+
 John | Johnny    |
 John | Jane      |
 Mark | Marky     |
 Mark | Mark Jr   |
 Mark | Maria     |
------+-----------+