Splitting a comma-separated field in Postgresql and doing a UNION ALL on all the resulting tables
27,986
This should give you the output you're looking for:
SELECT
yourTable.ID,
regexp_split_to_table(yourTable.fruits, E',') AS split_fruits
FROM yourTable
EDIT: Fixed the regex.
Related videos on Youtube
Comments
-
John Horton over 4 years
I have a table that contains a field of comma separated strings:
ID | fruits ----------- 1 | cherry,apple,grape 2 | apple,orange,peach
I want to create a normalized version of the table, like this:
ID | fruits ----------- 1 | cherry 1 | apple 1 | grape 2 | apple 2 | orange 2 | peach
The postgresql 8.4 documentation describes a regexp_split_to_table function that can turn a single table:
SELECT foo FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog',E'\\s+') AS foo;
which gives you this:
foo -------- the quick brown fox jumped over the lazy dog (9 rows)
But that is just for a single field. What I want to do is some kind UNION applied to all the tables generated by splitting each field. Thank you.
-
Admin over 7 yearscan you please help me with stackoverflow.com/questions/40005974/…
-
Admin over 7 yearsi tried SELECT CATEGORI.ID, regexp_split_to_table(CATEGORI.CATEGORY, E',') AS split_fruits FROM CATEGORI;
-
Admin over 7 yearsit works but i want to filter duplicate data from it
-
BERA over 2 yearsWhat's the E for?