SQL: how count how many rows contain multiple keywords

Question

I have a dataset that looks like this:

And I would like to do a select query where I can set manually keywords (e.g. city, beautiful, rains, trees) and get a count of how many times these keywords appear. In this case the result should be

Answer 1

Assume that you have a table of keywords. You can then do:

select kw.word, count(d.city)
from keywords kw left join
     dataset d
     on d.description like concat('%', kw.word, '%')
group by kw.word;

The only problem with this is partial matches. So, "trees" would match the keyword "tree" (as well as "re" and "e" and so on). If you know you have no punctuation and can assume that words are separated by spaces, you can use:

select kw.word, count(d.city)
from keywords kw left join
     dataset d
     on concat(' ', d.description, ' ') like concat('% ', kw.word, ' %')
group by kw.word;