Splitting a comma-separated field in Postgresql and doing a UNION ALL on all the resulting tables

Question

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.

Michael Fredrickson · Accepted Answer · 2012-02-10T20:02:28.443

36

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.

edited Feb 10 '12 at 20:02

answered Feb 10 '12 at 18:12

can you please help me with http://stackoverflow.com/questions/40005974/convert-comma-seperated-db-items-to-isolated-rows – Oct 12 '16 at 18:53
i tried SELECT CATEGORI.ID, regexp_split_to_table(CATEGORI.CATEGORY, E',') AS split_fruits FROM CATEGORI; – Oct 12 '16 at 19:20
it works but i want to filter duplicate data from it – Oct 12 '16 at 19:20
What's the E for? – BERA Jan 25 '22 at 10:21

1 Answers1