match a string from a list of values and create new row

Question

This is for all SQL developers over here, this would be easy but thought to post. I have a table which has all country names in world. I have another table which has a sentence which would have more than one country name in it. How to extract the country names and place them in a separate row like below

Country_Universe_Table
America
India
Bhutan
Nepal
Iran

dataid Comments
1 America and India has signed a deal
2 UK, Nepal and Bhutan see cold weather

The output shall come as
dataid Country
1 America
1 India
2 UK
2 Nepal
2 Bhutan

Can you show sample data and expected output ? – Vivek S. May 07 '15 at 10:34 — Vivek S., May 07 '15 at 10:34
@vivek - I edited the question and included the dataset. – user1036204 May 07 '15 at 10:41 — user1036204, May 07 '15 at 10:41
@jarlh - 1 and 2 are unique ids – user1036204 May 07 '15 at 10:43 — user1036204, May 07 '15 at 10:43

Gordon Linoff · Answer 1 · 2015-05-07T10:59:35.480

The following is an imperfect method of doing what you want:

select c.dataid, cut.country
from Country_Universe_Table cut join
     comments c
     on c.comment like '%' || cut.country || '%'

This just looks for the country string in the comments table. However, it will match "American" to "America" and "UKELELE" to "UK". This may be close enough for what you need.

EDIT:

You can enhance this (in more recent versions of Postgres) by splitting the comment into words and then doing the comparison using a lateral join:

select c.dataid, cut.country
from comments c cross join lateral
     regexp_split_to_table(c.comment, E'[.,-() ]+') as w(word) join
     Country_Universe_Table cut
     on w.word = cut.country;

score 1 · Answer 2 · edited May 23 '17 at 12:21

Short:
Convert your string to an array and use the array contains operator @>:

SELECT c.dataid, cut.country
FROM   comments c
JOIN   country_universe_table cut
       ON string_to_array(c.comments, ' ') @> ARRAY[cut.country];

Your column should really be named comment (singular).

Faster:

SELECT c.dataid, cut.country
FROM   comments c
     , unnest(string_to_array(c.comments, ' ')) u(country)
JOIN   country_universe_table cut USING (country);

Split column into multiple rows in Postgres

match a string from a list of values and create new row

2 Answers2