CODEWITHSUNDEEP
stringpostgresqlsplit
GeoBeez
GeoBeez

Reputation: 1024

Splitting multiple string row to different rows

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

Upvotes: 0

Views: 50

Answers (1)

Dzmitry Savinkou
Dzmitry Savinkou

Reputation: 5190

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)

Upvotes: 1

Related Questions