Double LIKE check

Question

How would one go about creating query that will check comparison to a table in such manner than it needs two or more matches, regardless which but not same. I thought of creating a duplicate column

Col1 Col2
 A    A
 B    B
 C    C

and checking against both: LIKE CONCAT('%',table.col1,'%') and LIKE CONCAT('%',table.col2,'%') I am just not sure how to make sure that second LIKE isnt equivalent to first, because since they are equivalent both of the above statement will return positive. Yet I need to flag items like AB and not AA

Answer 1

Ok, try something like this:

select s.*
from sentences s join
     words w
     on s.sentence like concat('%', w.word, '%') 
group by s.sentence
having count(*) > 1

This does the join to get the matches, and then filters out sentences that have fewer than two matches.

The use of s.* with group by s.sentence uses a MySQL (mis)feature called Hidden Columns, which might be useful in this case.

Answer 2

With this approach, you could add another AND clause to enforce this:

-- ...
WHERE foo LIKE CONCAT('%',table.col1,'%')
AND foo LIKE CONCAT('%',table.col2,'%')
AND table.col1 <> table.col2

I'm not sure that using a duplicate column is the best approach, though.