How to make a column of text a new table with one row for every word in Snowflake?-CodePudding

So I have been trying to use REPLACE() in Snowflake to parse text data to make a new dataset but cannot get it to work.

Here's my data:

text                           date           ID
I am the one                   2021-01-02     001
harry potter I'd               2021-01-03     002

ID is unique to every row. And I want to change this to the following format within Snowflake

word   date         ID
I      2021-01-01   001
am     2021-01-01   001
the    2021-01-01   001
one    2021-01-01   001
harry  2021-01-03   002
potter 2021-01-03   002
I'd    2021-01-03   002

Delimiter would be the " ".

CodePudding user response：

Snowflake has split_to_table() for simple delimiters:

select s.value as word, t.date, t.id
from t, lateral
     split_to_table(t.text, ' ') s;