Query to find most common names in content, from a defined list

Question

Hi, I have been struggling to find a way to rank blog post articles by popularity of names that appear in them.

Here's what I'm trying to achieve: I have a blog where celebrity names commonly appear. I would like to count how many times these celebrity names appear in the blog titles, and rank the output by most popular celebrity (name appears the most).

My idea is to run a query that counts how many times names from a table(table of celebrity names) appear in the posts. This is where I'm having trouble. Can't figure out the best query to make this work.

Any help? Do I need to use an Array?

Answer 1

A possible solution is to index the data first and then search. I've done this via Lucene and works like a charm.

Answer 2

Maybe something like -- adjust for your dialect of SQL.

select c.name, count(*) as occurrences
from celebrities c
inner join articles a on a.title like '%' + c.name + '%'
group by c.name