MySQL get full word match (not characters) at any position from sentence by relevancy

Question

I am trying to get exact word (sub-strings) match from sentences (strings) containing at least one exact word match (best if not articles or gerunds) stored in a database at any position in the sentence as follow:

SELECT q 
FROM q 
WHERE q LIKE '%$q%' OR '$q' LIKE CONCAT('%', q, '%')
ORDER BY CASE WHEN q = '$q' THEN 0  
              WHEN q LIKE '$q%' THEN 1  
              WHEN q LIKE '%$q%' THEN 2  
              WHEN q LIKE '%$q' THEN 3  
              ELSE 4
         END, q ASC 
         LIMIT 10;

But it returns the following results for the query with value $q = "best seo company for small business";:

best seo company for small business
best seo
best seo company
mpa
seo
seo com
seo company
small
small b
small bu

Desired outputs:

best seo company for small business
best seo
best seo company
seo
seo company
small

Undesired outputs:

mpa
seo com
small b
small bu

How do I exclude the undesired outputs?

Note: The questions below:

Search for "whole word match" in MySQL

Make MySQL LIKE Return Full Word matches Only

MySQL Finding Substring Matches and Group by Match Full Word

Suggests some answers which are not relevant for the solution I am looking for.

What defines a word? If a word is everything surrounded by exactly a "space" or is at the begin and end of the string (so a `.` will be part of a word), you can simply add a space around every `$q` and `q` (or use `RLIKE`). If a word is defined more complicated, and/or if your data contains such additional delimiters too that have to be ignroed, it will be harder. A [fulltext search](https://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html) might work, although it doesn't care about the order of words, which might be an advantage or will exclude it, depending on your requirements. — Solarflare, Mar 08 '18 at 10:30
Change `SELECT q` to `SELECT CASE ... END, q` -- this will help you narrow down the problem(s). — Rick James, Mar 14 '18 at 15:53

score 1 · Accepted Answer · answered Mar 08 '18 at 16:20

The solution to this is can be REGEXP q+ It matches any string containing at least one q.

Although it will match seo company with:

affordable seo company
atlanta seo company
austin seo company
automotive seo company
best atlanta seo company
best local seo company
best los angeles seo company
best organic seo company
best real estate seo company
best seo company
best seo company for google
best seo company for lawyers
best seo company for small business
best seo company in india
best seo company in the usa
best seo company in the world
best seo company india
best seo company los angeles
best seo company miami
boise seo company
boston seo company
california seo company
charleston seo company
charlotte seo company
cheap seo company

It can't match best seo company for small business with any of the above terms except itself.

Try this:

SELECT q 
FROM q 
WHERE q REGEXP '$q+'
ORDER BY CASE WHEN q = '$q' THEN 0  
              WHEN q LIKE '$q%' THEN 1  
              WHEN q LIKE '%$q%' THEN 2  
              WHEN q LIKE '%$q' THEN 3  
              ELSE 4
         END, q ASC 
         LIMIT 10

It helps, but it's not the solution to my problem. I want all the matches even if only one word matches in the sentences. — Evil Tech, Mar 08 '18 at 16:39
Why the `+`? I don't think he wants "best seo company for small businesssssss" — Rick James, Mar 14 '18 at 15:54

score -1 · Answer 2 · answered Mar 14 '18 at 15:59

-1

WHERE '$q' REGEXP '[[:<:]]q[[:>:]]'

But, beware. If you have any punctuation in $q, you could get a syntax error. So, be sure to escape it.

answered Mar 14 '18 at 15:59

Rick James

135,179
13
127
222

MySQL get full word match (not characters) at any position from sentence by relevancy

2 Answers2