MySql select with like and give a weight based on number of substrings found

Question

I have a table (books)

id, title

I made a select with REGEXP match

select title 
from books 
where title REGEXP "word1|word2|...|wordn"

How can I achieve how many words I found in title in order to obtain a query like that?

select title, numberofwordsfound 
from books 
where title REGEXP "word1|word2|...|wordn"

Thanks in advance to everyone :)

Answer 1

One option uses a derived table to list the words, then aggregation:

select b.id, b.title, count(*) no_matches
from books b
inner join (
    select 'word1' word
    union all select 'word2'
    union all select 'word3'
) w on b.title like concat('%', w.word, '%')
group by b.id, b.title
order by no_matches desc

In very recent versions of MySQL, you can use the VALUES() row-constructor to enumerate the words, which shortens the query:

select b.id, b.title, count(*) no_matches
from books b
inner join (values(row('word1'), row('word2'), row('word3')) b(word)
    on b.title like concat('%', w.word, '%')
group by b.id, b.title
order by no_matches desc

This assumes that the "words" are just that - words. If they contain regular expression patterns, they you need to use a regex match instead of like:

on b.title regexp w.word

Answer 2

You can use a trick with regexp_replace() and capture groups:

select title,
       length(regexp_replace(title, '(word1|word2|...|wordn)', '$1x')) - length(title) as num_matches
from books 
where title REGEXP 'word1|word2|...|wordn';

Here is a db<>fiddle.