Categories:
String & Binary Functions (General) , Table Functions

STRTOK_SPLIT_TO_TABLE

Tokenizes a string with the given set of delimiters and flattens the results into rows.

See also:
STRTOK, STRTOK_TO_ARRAY

Syntax

STRTOK_TO_SPLIT_TO_TABLE(<string> [,<delimiter_list>])

Arguments

Required:

string
Text to be tokenized.

Optional:

delimiter_list
Set of delimiters. Optional. Default value is a single space character.

Returns

This returns a table.

The returned rows consist of a fixed set of columns:

+-----+-------+------ +
| SEQ | INDEX | VALUE |
|-----+------+--------|
SEQ:A unique sequence number associated with the input record; the sequence is not guaranteed to be gap-free or ordered in any particular way.
INDEX:The index of the element. One based.
VALUE:The value of the element of the flattened array.

Note

The columns of the original (correlated) table that was used as the source of data for this function are also accessible. If a single row from the original table resulted in multiple rows in the flattened view, the values in this input row are replicated to match the number of rows produced by STRTOK_SPLIT_TO_TABLE.

Examples

Here is a simple example on constant input.

SELECT table1.value 
    FROM table(strtok_split_to_table('a.b', '.')) AS table1
    ORDER BY table1.value;
+-------+
| VALUE |
|-------|
| a     |
| b     |
+-------+

The following two examples use the table and data created below:

CREATE OR REPLACE TABLE splittable (v VARCHAR);
INSERT INTO splittable (v) VALUES ('a b'), ('cde'), ('f|g'), ('');

This example shows usage of the function as a correlated table:

SELECT * 
    FROM splittable, LATERAL STRTOK_SPLIT_TO_TABLE(splittable.v, ' ')
    ORDER BY SEQ, INDEX;
+-----+-----+-------+-------+
| V   | SEQ | INDEX | VALUE |
|-----+-----+-------+-------|
| a b |   1 |     1 | a     |
| a b |   1 |     2 | b     |
| cde |   2 |     1 | cde   |
| f|g |   3 |     1 | f|g   |
+-----+-----+-------+-------+

This example is the same as the preceding, except that it specifies multiple delimiters:

SELECT * 
    FROM splittable, LATERAL STRTOK_SPLIT_TO_TABLE(splittable.v, ' |')
    ORDER BY SEQ, INDEX;
+-----+-----+-------+-------+
| V   | SEQ | INDEX | VALUE |
|-----+-----+-------+-------|
| a b |   1 |     1 | a     |
| a b |   1 |     2 | b     |
| cde |   2 |     1 | cde   |
| f|g |   3 |     1 | f     |
| f|g |   3 |     2 | g     |
+-----+-----+-------+-------+