Menu
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.
796
We can use any of the string to split it; we can also use a column name as a substring to split the data from the column. Delimiter argument is used to split the string into sub-parts by using a split_part function in PostgreSQL. We can split the string into a number of parts using delimiter.
The PostgreSQL SPLIT_PART() function is used to split a string from a specific delimiter and these queries return the nth substring. Let's analyze the above syntax: The string argument is the string to be split. The delimiter is a string used as the delimiter for splitting.
regexp_split_to_table() is a system function for splitting a string into a table using a POSIX regular expression as the delimiter.
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.
73
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
