STRTOK_SPLIT_TO_TABLE

Translation specification for the STRTOK_SPLIT_TO_TABLE function to its Snowflake equivalent query

Description

Splits a string into a table using the provided delimiters. For more information check STRTOK_SPLIT_TO_TABLE.

[TD_SYSFNLIB.] STRTOK_SPLIT_TO_TABLE ( inkey, instring, delimiters )
  RETURNS ( outkey, tokennum, token )

Sample Source Patterns

Setup data

Teradata

Query

CREATE TABLE strtokTable
(
	col1 INTEGER,
	col2 VARCHAR(100)
);

INSERT INTO strtokTable VALUES(4, 'hello-world-split-me');
INSERT INTO strtokTable VALUES(1, 'string$split$by$dollars');

Snowflake

Query

CREATE TABLE PUBLIC.strtokTable
(
	col1 INTEGER,
	col2 VARCHAR(100)
);

INSERT INTO PUBLIC.strtokTable
VALUES (4, 'hello-world-split-me');

INSERT INTO PUBLIC.strtokTable
VALUES (1, 'string$split$by$dollars');

STRTOK_SPLIT_TO_TABLE transformation

Teradata

Query

SELECT outkey, tokennum, token FROM table(STRTOK_SPLIT_TO_TABLE(strtokTable.col1, strtokTable.col2, '-$')
RETURNS (outkey INTEGER, tokennum INTEGER, token VARCHAR(100))) AS testTable
ORDER BY outkey, tokennum;

Result

outkey |tokennum | token  |
-------+---------+--------+
1      |1        |string  |
-------+---------+--------+
1      |2        |split   |
-------+---------+--------+
1      |3        |by      |
-------+---------+--------+
1      |4        |dollars |
-------+---------+--------+
4      |1        |hello   |
-------+---------+--------+
4      |2        |world   |
-------+---------+--------+
4      |3        |split   |
-------+---------+--------+
4      |4        |me      |

Snowflake

Query

SELECT
    CAST(PUBLIC.strtokTable.col1 AS INTEGER) AS outkey,
    CAST(INDEX AS INTEGER) AS tokennum,
    CAST(VALUE AS VARCHAR) AS token
    FROM
    PUBLIC.strtokTable,
    table(STRTOK_SPLIT_TO_TABLE(PUBLIC.strtokTable.col2, '-$')) AS testTable
    ORDER BY outkey, tokennum;

Result

outkey |tokennum | token  |
-------+---------+--------+
1      |1        |string  |
-------+---------+--------+
1      |2        |split   |
-------+---------+--------+
1      |3        |by      |
-------+---------+--------+
1      |4        |dollars |
-------+---------+--------+
4      |1        |hello   |
-------+---------+--------+
4      |2        |world   |
-------+---------+--------+
4      |3        |split   |
-------+---------+--------+
4      |4        |me      |

Known Issues

No known issues.

Related EWIs

1. MSCEWI2056: Column added to the Select's columns to avoid errors when referencing strtok_split_to_table function.