Splitting multiple string row to different rows

Question

I have a table in Postgresql. one of the fields have multiple string, I want to put this string in different rows. I used:

id  lng  lat  descriptions
1   0.98 51   walk, wedding, bus

and insert into another table like this:

id  lng   lat  descriptions
1   0.98   51   walk
1   0.98   51   wedding
1   0.98   51   bus

Dzmitry Savinkou · Accepted Answer · 2016-04-19 18:38:28Z

1

Use unnest() with regexp_split_to_array():

WITH tb(id,lng,lat,descriptions) AS ( VALUES
  (1,0.98,51,'walk, wedding, bus')
)
SELECT id,lng,lat,trim(unnest(regexp_split_to_array(descriptions,','))) AS descriptions FROM tb;

Result:

 id | lng  | lat | descriptions 
----+------+-----+--------------
  1 | 0.98 |  51 | walk
  1 | 0.98 |  51 | wedding
  1 | 0.98 |  51 | bus
(3 rows)

answered Apr 19, 2016 at 18:38

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

If you are using regex then regexp_split_to_table() should be faster then a combination of unnest() and regexp_split_to_array(). But using a regex is not needed here and string_to_array() will be a faster than regexp_split_to_array()

Collectives™ on Stack Overflow

Splitting multiple string row to different rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related