How to split row into many rows in postgresql

Question

I have got a table with two columns: ID and cars. Sample data looks like:

ID  |  Cars
-----------------
1   |  opel, honda and land rover
2   |  ford and porshe, damaged
3   |  volkswagen
4   |  opel, seat, damaged

I would like to split it into:

ID  |  Cars
-----------------
1   |  opel
1   |  honda
1   |  land rover
2   |  ford
2   |  porshe, damaged
3   |  volkswagen
4   |  opel
4   |  seat, damaged

So the delimiter is and or , but not , damaged How to use regex to split it in postgresql?

EDIT:

And how to make it working for records like

5 | land rover and opel, and ford
6 | ford; mazda and toyota

What kind of car is "porshe"?

user330315
– user330315

2016-02-20 17:50:09 +00:00
Commented Feb 20, 2016 at 17:50 — user330315
– user330315, Commented Feb 20, 2016 at 17:50

Joachim Isaksson · Accepted Answer · 2016-02-20 18:33:12Z

4

You can use regexp_split_to_table with a negative lookahead for damaged;

SELECT "ID", regexp_split_to_table("Cars", '((, (?!damaged))| and )') "Cars" 
FROM mytable;

 ID |      Cars
----+-----------------
  1 | opel
  1 | honda
  1 | land rover
  2 | ford
  2 | porshe, damaged
  3 | volkswagen
  4 | opel
  4 | seat, damaged
(8 rows)

An SQLfiddle to test with.

EDIT: For your new examples, the regex had to be tweaked a bit;

SELECT "ID", regexp_split_to_table("Cars", '(([,;] (?!damaged))|[,;]? and )') "Cars" 
FROM mytable;

Another SQLfiddle.

edited Feb 20, 2016 at 18:33

answered Feb 20, 2016 at 18:01

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

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

6 Comments

Mateusz Over a year ago

Thanks for quick reply! And how to add new rows? because this is just select query. Thanks!

Joachim Isaksson Over a year ago

@Mateusz Maybe you could add a new question for that with more info and examples what you want to do, the question does not even mention insertion of new rows or what the input or resulting format should be.

Mateusz Over a year ago

then I would duplicate my question, i think that is not okey on stackoverflow

Joachim Isaksson Over a year ago

@Mateusz Well, you have the select part that the question was about, I was thinking about the insertion you asked about in the comments that wasn't mentioned in the question :)

Joachim Isaksson Over a year ago

@Mateusz Since it'd be a substantial edit with new functionality, it's probably better to ask a new (self contained) question. You can always add a link to this question if needed as part of your explanation.

|

Collectives™ on Stack Overflow

How to split row into many rows in postgresql

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related