1

Looking for an elegant way in PostgreSQL to split sentences contained in a table into sub-sentences, assuming that the delimiter is ' '. I would like to use a recursive query (like here) but I can't build it. Can someone help?

I have the following table:

id1 | yes we can
id2 | beautiful mind
id3 | birds can fly

and I want like to get something like this:

id1 | yes we can
id1 | yes
id1 | we
id1 | can
id1 | yes we
id1 | we can
id2 | beautiful mind
id2 | beautiful
id2 | mind
id3 | birds can fly
id3 | birds can
id3 | can fly

A tutorial is accessible here.

Gilbou
  • 5,244
  • 6
  • 24
  • 27
  • The functions you need are: To normalize the compound strings `generate_subscripts()` , `string_to_array()` , `unnest()` (before the recursive query, which will yield arrays), and to rebuild the strings from the parts: `array_to_string()` after the recursive query. – joop May 22 '15 at 15:25
  • Hi joop. Thanks for your help! Do you think you could write the query for me (based on my example)? – Gilbou May 22 '15 at 15:33
  • 2
    I already wrote it. But I won't do your homework for you. First show us some effort. ( I looked up the functions for you ...) – joop May 22 '15 at 15:35
  • This post http://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number/8767450#8767450 gives me a clue too. Joop, thanks for your help. I will think about it and come back next week :-) – Gilbou May 22 '15 at 16:00
  • `SELECT id , generate_subscripts(string_to_array(elements, ','),1) AS seq , unnest(string_to_array(elements, ',')) AS elem FROM myTable ORDER BY id,seq ;` is an easy solution to http://stackoverflow.com/questions/8760419/postgresql-unnest-with-element-number/8767450#8767450 – joop May 22 '15 at 16:25

0 Answers0