CODEWITHSUNDEEP
sqlpostgresql
vegas red
vegas red

Reputation: 269

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

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.

Upvotes: 5

Views: 9121

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78423

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)

Upvotes: 15

Related Questions