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_SPLIT_TO_TABLE(<string> [,<delimiter_list>])

Arguments¶

Required:

string: Text to be tokenized.

Optional:

delimiter_list: Optional set of delimiters. The default value is a single space character.

Output¶

This function returns the following columns:

Column name	Data type	Description
SEQ	NUMBER	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	NUMBER	The one-based index of the element.
VALUE	VARCHAR	The value of the element of the flattened array.

Note

The query can also access the columns of the original (correlated) table that served as the source of data for this function. 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 this function.

Usage notes¶

If string is an empty string, NULL, or contains only delimiter characters, the function returns no rows. This behavior differs from SPLIT_TO_TABLE, which returns one row for an empty string.
Because the function returns no rows for these inputs, the corresponding input rows are omitted from the result of a lateral join, even when you specify LEFT JOIN LATERAL. To keep every input row, use STRTOK_TO_ARRAY with FLATTEN and specify OUTER => TRUE. For an example, see Keep input rows that produce no tokens.

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

Create a table and insert data:

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

+-----+
| V   |
|-----|
| a b |
| cde |
| f|g |
|     |
+-----+

You can use the LATERAL keyword with the STRTOK_SPLIT_TO_TABLE function so that the function executes on each row of the splittable_strtok table as a correlated table:

SELECT *
  FROM splittable_strtok, LATERAL STRTOK_SPLIT_TO_TABLE(splittable_strtok.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_strtok, LATERAL STRTOK_SPLIT_TO_TABLE(splittable_strtok.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     |
+-----+-----+-------+-------+

Create another table that contains authors in one column and some of their book titles in another column. In the table data, the book titles might be separated by a comma or a semi-colon:

CREATE OR REPLACE TABLE authors_books_test2 (author VARCHAR, titles VARCHAR);
INSERT INTO authors_books_test2 (author, titles) VALUES
  ('Nathaniel Hawthorne', 'The Scarlet Letter ; The House of the Seven Gables;The Blithedale Romance'),
  ('Herman Melville', 'Moby Dick,The Confidence-Man');
SELECT * FROM authors_books_test2;

+---------------------+---------------------------------------------------------------------------+
| AUTHOR              | TITLES                                                                    |
|---------------------+---------------------------------------------------------------------------|
| Nathaniel Hawthorne | The Scarlet Letter ; The House of the Seven Gables;The Blithedale Romance |
| Herman Melville     | Moby Dick,The Confidence-Man                                              |
+---------------------+---------------------------------------------------------------------------+

Use the LATERAL keyword and the SPLIT_TO_TABLE function to run a query that returns a separate row for each title. In addition, use the TRIM function to remove leading and trailing spaces from the titles. Note that the SELECT list includes the fixed value column that is returned by the function:

SELECT author, TRIM(value) AS title
  FROM authors_books_test2, LATERAL STRTOK_SPLIT_TO_TABLE(titles, ',;')
  ORDER BY author;

+---------------------+-------------------------------+
| AUTHOR              | TITLE                         |
|---------------------+-------------------------------|
| Herman Melville     | Moby Dick                     |
| Herman Melville     | The Confidence-Man            |
| Nathaniel Hawthorne | The Scarlet Letter            |
| Nathaniel Hawthorne | The House of the Seven Gables |
| Nathaniel Hawthorne | The Blithedale Romance        |
+---------------------+-------------------------------+

Keep input rows that produce no tokens¶

STRTOK_SPLIT_TO_TABLE returns no rows when the input string is empty or NULL, so a lateral join omits those input rows, even with LEFT JOIN LATERAL. Create a table that contains an empty string and a NULL value:

CREATE OR REPLACE TABLE strtok_outer_test (id INT, s VARCHAR);
INSERT INTO strtok_outer_test VALUES (1, ''), (2, 'a b c'), (3, NULL);

The following query returns rows only for id 2:

SELECT t.id, s.value
  FROM strtok_outer_test t
    LEFT JOIN LATERAL STRTOK_SPLIT_TO_TABLE(t.s, ' ') s
  ORDER BY t.id, s.value;

+----+-------+
| ID | VALUE |
|----+-------|
|  2 | a     |
|  2 | b     |
|  2 | c     |
+----+-------+

To keep every input row, use STRTOK_TO_ARRAY with FLATTEN and specify OUTER => TRUE:

SELECT t.id, f.value::VARCHAR AS value
  FROM strtok_outer_test t,
    LATERAL FLATTEN(INPUT => STRTOK_TO_ARRAY(t.s, ' '), OUTER => TRUE) f
  ORDER BY t.id, value;

+----+-------+
| ID | VALUE |
|----+-------|
|  1 | NULL  |
|  2 | a     |
|  2 | b     |
|  2 | c     |
|  3 | NULL  |
+----+-------+