通过包含特定单词来搜索MySql REGEXP多个单词(MySql REGEXP multiple words search by including specific words)

通过包含特定单词来搜索MySql REGEXP多个单词(MySql REGEXP multiple words search by including specific words)

我有一个关键字coloumn的数据库

需要在用户查询的基础上搜索数据库。

每个关键词最后都有词“outlet”，但用户只搜索“gul ahmad”而不是“gul ahmad outlet”。 为此，我使用以下查询和事情工作正常，以获得结果，并找到完整的结果“古尔艾哈迈德出口”

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

现在我有两个问题1.如果单词“插座是在查询词之间，那么它找不到这个词，例如，如果用户搜索”kohistan lahore“，数据库有一个名为”kohistan outlet lahore“的插座，但它没有找到数据库中的关键字并返回空白。如何告诉数据库在“开始”或“结束”之间包含“出口”以查找和匹配结果。

如果某个用户搜索“nabeel's outlet”数据库有它，但由于“'”，这个查询返回空白而没有任何结果。

I have a databse with keywords coloumn

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 1. 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 athe 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.

最满意答案

你可以做的是，你可以将你的列值与你的搜索表达式的第一个单词相匹配（即nabeel的出口）。 我相信这样你就可以覆盖你所有的场景。

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

看看这个小提琴并测试自己： http : //sqlfiddle.com/#!9/b3000/21

希望能帮助到你。

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.