seo Question posted in
Some basic information to get you started with this can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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. 0 
    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