Count number of LIKE-matches per Entry

match, mysql, string

Solution

In MySQL, boolean expressions can be used as integers -- with 0 for false and 1 for true. So, the following works:

SELECT p.*,
       ((name LIKE '%samsung%') + (name LIKE '%galaxy%') + (name LIKE '%s4%')) as hits
FROM   myprods p
WHERE  name LIKE '%samsung%' OR name LIKE '%galaxy%' OR name LIKE '%s4%';

EDIT:

If MySQL, you can also express this query as:

SELECT p.*,
       ((name LIKE '%samsung%') + (name LIKE '%galaxy%') + (name LIKE '%s4%')) as hits
FROM   myprods p
HAVING hits > 0;

The use of `having` in this context is a SQL extension and doesn't work in other databases. But, it does allow a query to refer to a column alias for filtering, without using a subquery.

Problem

I try to explain what I mean by matches on a simple example: I have a table `myprods` like this: ``` id | name 1 galaxy s4 mini 2 samsung galaxy s4 3 galaxy galaxy s3 4 iphone 4s 5 apple iphone 4s ``` I'd have this query so far: ``` SELECT * FROM myprods WHERE name LIKE "%samsung%" OR name LIKE "%galaxy%" OR name LIKE "%s4%" ``` The result of my query is: ``` id | name 1 galaxy s4 mini 2 galaxy s4 3 galaxy galaxy s3 ``` and now I'd not only return the matched rows but also the number of hits. ONE hit would be if ONE of the LIKE-phrases applies to the row. So in this case I would expect the hits to be 0, 1, 2 or 3. That means: the LIKE phrase `"%galaxy%"` should NOT be counted as two hits for the id=3, it just counts as "hit" or "not hit". The expected result would now be: ``` id | name | hits 1 galaxy s4 mini 2 2 samsung galaxy s4 3 3 galaxy galaxy s3 1 ``` Possible in a performant mysql phrase? Or should I rather select only the rows with my query above and then match the single substrings to the returned names via `strpos` in PHP?

Original source