Split_Part() Entire Column in Redshift
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...
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 <> '';
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
