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.
2
Answers
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:It can’t match
best seo company for small business
with any of the above terms except itself.Try this:
But, beware. If you have any punctuation in
$q
, you could get a syntax error. So, be sure to escape it.