SPLIT_PART() entire column in redshift
Publish date: 2024-05-07
![](https://cdn.statically.io/img/cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon@2.png)
How do I split_part an entire column(word-by-word)? I am trying to split the column "answer" into each word.
eg this is my dataset:
name | answer |
---|---|
Kate | i love cheese |
Tom | i love bacon & eggs |
this is what i want:
name | split_answer |
---|---|
Kate | i |
Kate | love |
Kate | cheese |
Tom | i |
Tom | love |
Tom | bacon |
Tom | & |
Tom | eggs |
this is my query:
SELECT name, split_part(answer, ' ') AS split_asnwer FROM table
1 Answer
Split_part() can take 3 arguments - string, delimiter, and part number.
So you need to cross join with a numbers table that has all the integer values from 1 to the max number of parts in any string. You can generate this numbers table with a recursive CTE or some like to just have a numbers table on hand.
The query will look something like (untested and off the cuff):
with recursive nums(n) as ( select 1 as n union all select n + 1 from nums where n < (select max(LEN(answer) - LEN(REPLACE(answer, ' ', '')) + 1) from table) ) select name, split_part(answer, ' ', n) AS split_answer FROM table cross join nums where split_answer <> '';
2ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobm1uZWuDeIOOrKeloaRivaK%2B02acp6yZp7Jur86lrKamXZ67br7EnaqhoZap