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

See also:: SPLIT_PART

Syntax¶

SPLIT(<string>, <separator>)

Copy

Arguments¶

string: Text to be split into parts.
separator: Text to split string by.

Returns¶

The data type of the returned value is ARRAY.

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"                   |
| ]                       |
+-------------------------+

Copy

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

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

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

Copy

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     |
------+-----------+

Copy