How do I split_part an entire column(word-by-word)? I'm trying to cut up the column "answer" into each word.
eg this is my dataset:
| name | answer |
|---|---|
| Kate | i love cheese |
| Tom | i like bacon & eggs |
this is what i need:
| title | split_answer |
|---|---|
| Kate | i |
| Kate | love |
| Kate | cheese |
| Tom | i |
| Tom | love |
| Tom | bacon |
| Tom | & |
| Tom | eggs |
this is my question:
SELECT name, split_part(solution, ' ') AS split_asnwer FROM table 1 Answer
Split_part() can take 3 arguments - string, delimiter, and part number.
So you want to pass join with a numbers table that has all of the integer values from 1 to the max number of portions in any string. You can generate this numbers desk with a recursive CTE or some like to simply have a numbers desk readily available.
The query will look something like (untested and rancid the cuff):
with recursive nums(n) as ( select 1 as n union all select n + 1 from nums where n < (make a choice max(LEN(solution) - LEN(REPLACE(resolution, ' ', '')) + 1) from desk) ) choose identify, split_part(resolution, ' ', n) AS split_answer FROM table pass sign up for nums where split_answer <> ''; ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobm1uZWuDeIOOrKeloaRivaK%2B02acp6yZp7Jur86lrKamXZ67br7EnaqhoZap