Postgres: Extracting specific words from free text column

Question

I have a table below

| id |          description           |
+----+--------------------------------+
| 1  | Added: Apple, Grape and Orange |
| 2  | Orange                         |
| 3  | Removed: Plum and Grape        |

Then, I have another table that contains the list of fruits

| id | name   |
+----+--------+
| 1  | apple  |
| 2  | orange |
| 3  | plum   |

With these two tables, is it possible to extract every word from the first table on description column that matches all the fruit names?

I want the output like this

| id |          description           |  word  |
+----+--------------------------------+--------+
| 1  | Added: Apple, Grape and Orange | Apple  |
| 1  | Added: Apple, Grape and Orange | Orange |
| 2  | Orange                         | Orange |
| 3  | Removed: Plum and Grape        | Plum   |

Thank you.

Laurenz Albe · Accepted Answer · 2021-03-04 03:13:49Z

1

That would be a simple inner join with pattern matching as join condition:

SELECT mytable.id,
       mytable.description,
       fruit.name AS word
FROM mytable
   JOIN fruit ON mytable.description ILIKE '%' || fruit.name || '%';

If both tables are large, that will be pretty slow, since it requires a nested loop join. You could use a trigram index on mytable(description) to speed up the query.

answered Mar 4, 2021 at 3:13

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

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

Collectives™ on Stack Overflow

Postgres: Extracting specific words from free text column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related