Split one string with commas into columns
10,393
Seems to me you would simply use split_part
function to create your columns, like so:
SELECT
split_part(Block, ',', 1) AS Block1,
split_part(Block, ',', 2) AS Block2,
split_part(Block, ',', 3) AS Block3,
split_part(Block, ',', 4) AS Block4,
split_part(Block, ',', 5) AS Block5,
split_part(Block, ',', 6) AS Block6,
split_part(Block, ',', 7) AS Block7,
split_part(Block, ',', 8) AS Block8,
split_part(Block, ',', 9) AS Block9
FROM
your_table
Author by
Tai Ngo
Updated on June 17, 2022Comments
-
Tai Ngo almost 2 years
For example, I have the following table:
| Block | | abcdefgh,12kjkjkj,231wewoxyz|
How can I convert it into:
| Block1 | Block2 | Block3 | | abcdefgh | 12kjkjkj | 231wewoxyz |
Note: - Each "Block" has a maximum of 8 commas (and hence can be split into 9 smaller blocks). The number of commas varies; some have 8 commas, some have 4, some have 7, etc. Each smaller block will occupy a column.
I use Presto, btw.
Thanks!