mysqlsql-likeword-boundaries

Get most matched result on top in mysql LIKE query


Say I have a table like this:

Table: businesses

+----+------------------------+
| id | name                   |
+----+------------------------+
| 1  | Carolyn Wong Cakes     |
| 2  | Cupcakin Cackes Shop   |
| 3  | Wong Cackes Shop       |
| 4  | Indie Cakes & Pastries |
+----+------------------------+

I want to get the best matched result while I search for some words inside name. for example I want to search: Wong Cackes Shop which is 3 words.
I use this query for above search:

SELECT * FROM businesses WHERE ( 
  name LIKE '% Wong %' OR 
  name LIKE '% Cackes %' OR
  name LIKE '% Shop %'
)

I expect record 3 to be the first matched result but the result is:

  1. Carolyn Wong Cakes // with 2 matching words
  2. Cupcaking Cackes Shop // with 2 matching words
  3. Wong Cackes Shop // with 3 matching words

How can I search words and get records with most matched result on top of other results ?

Like this:

  1. Wong Cackes Shop // with 3 matching words
  2. Cupcaking Cackes Shop
  3. Carolyn Wong Cakes

EDIT: My word-boundaries LIKE method also has a problem. it won't get results which start or end with one of 3 words. because of those spaces in LIKE '% word %'
for example:
Wong[space] // does not match
[space]Wong[space] match
[space]Wong // does not match

Thanks.


Solution

  • following should do the trick

    SELECT 
      name,
      (( name LIKE '%Wong%')+( name LIKE '%Cackes%') +(name LIKE '%Shop%')) as total
    FROM businesses WHERE ( 
      name LIKE '%Wong%' OR 
      name LIKE '%Cackes%' OR
      name LIKE '%Shop%'
    )
    ORDER BY total DESC
    

    You can even delete the where clause here, since you're just interested in the total number of hits.
    In order to overcome the problem of the spaces, just eliminate them in the like-clauses. %wong% will match [space]wong OR [space]wong[space] or wong[space]