[SOLVED] MySql REGEXP multiple words search by including specific words

MySql REGEXP multiple words search by including specific words

I have a database table with a keywords column.

I need to search the database on the basis of query done by user.

Every keyword has word "outlet" at the end but user will only search "gul ahmad" not "gul ahmad outlet". For this, I used following query and things worked fine to get results and found complete result "Gul Ahmad Outlet"

$sql = "SELECT keywords FROM table WHERE keywords REGEXP '([[:blank:][:punct:]]|^)$keyword([[:blank:][:punct:]]|$)'";

Now I have 2 issues:

If the word "outlet is in between the query words then it does not find the word. e.g if user search "kohistan lahore", database has an outlet named "kohistan outlet lahore" but it does not find the keyword in database and returns empty. How to tell database to include "outlet" in between, at the start or at the end to find and match the result.
If some user search "nabeel's outlet" database has it but due to " ' " this query returns empty without any result.

Solution

What you can do is that you can match your column values with just the first word of your search expression(i.e nabeel's outlet). I believe this way you will be able to cover all your scenarios.

select 
      * 
from `outlets` 
    where REPLACE(`name`,'\'','') regexp SUBSTRING_INDEX('nabeels outlet', ' ', 1)

Look at this fiddle and test yourself : http://sqlfiddle.com/#!9/b3000/21

Hope it helps.