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 and ARRAY_SIZE ).

Syntax ¶

SPLIT(<string>, <separator>)
Arguments¶
string
Text to be split into parts.
separator
Text to split string by.
Collation details¶
This function does not support the following collation specifications:
pi  (punctuation-insensitive).
cs-ai (case-sensitive, accent-insensitive).
The values in the output array do not include a collation specification and therefore do not support further
collation operations.
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     |
------+-----------+