How to split a value into multiple rows on the newline char in PostgreSQL?

Question

I have have table named BookInfo with the following structure:

|id  |book_name  |description  |
--------------------------------
|1   |book 1     |harry        |
|    |           |potter       |
|    |           |Part 2       |
|2   |...        |             |

How could I split the row (id=1) into multiple rows on the newline character (so that harry \n potter \n Part 2 would be separated into 3 different records: harry, potter and Part 2 using a query?

To reiterate, the resultset would look something like this:

|id  | description   |
----------------------
|1   |harry          | 
|2   |potter         |
|3   |Part 2         |

Any help will be much appreciated, thank you.

Denis de Bernardy · Accepted Answer · 2013-05-29 09:35:00Z

15

You're looking for regexp_split_to_table():

http://www.postgresql.org/docs/9.2/static/functions-string.html

select regexp_split_to_table('hello world', E'\\s+');

hello
world

(2 rows)

answered May 29, 2013 at 9:35

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

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

Collectives™ on Stack Overflow

How to split a value into multiple rows on the newline char in PostgreSQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related