skip to Main Content

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


  1. 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
    
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search