搜索查询中大多数匹配的单词-Mostmatchingwordsinsearchquery

I have user table (eg. UserMst) is mysql like

我有用户表(例如UserMst)是mysql之类的

UserID  FirstName  LastName  FullName (expression column)   
------  ---------  --------  ----------------------------
1       Manish     Patel     Manish Patel
2       Dharmesh   Patel     Dharmesh Patel 
3       Patel      Pranay    Patel  Pranay
4       Patel      Ankita    Patel Ankita

Now I am fetching result as follows:

现在我获取结果如下:

Select * from UserMst
where (    FirstName like 'patel dha%'
        OR LastName  like 'patel dha% 
        OR FullName  like '%patel dha% 
        OR FullName  like '%patel% 
        OR FullName  like '%dha% )
Order by ( case when FirstName like 'patel dha%' then 1
                WHEN LastName  like 'patel dha%' then 2 
                WHEN FirstName like '%patel dha' then 3
                WHEN LastName  like '%patel dha%'then 4 end ),
          FullName   

This query return result perfect, but I need more nearest ordered result. In this case If some one is enter keyword 'patel dha' (here user can leave more space between 2 words as well as he can type as many words too e.g. 'patel dha' or 'patel dha pran' ) it means he is trying to find/search user 1 (eg. Dharmesh patel) This records should come first.

此查询返回结果完美,但我需要更近的有序结果。在这种情况下,如果有人输入关键词'patel dha'(这里用户可以在两个单词之间留出更多空格,也可以输入更多单词,例如'patel dha'或'patel dha pran')这意味着他正在尝试查找/搜索用户1(例如Dharmesh patel)此记录应该首先出现。

my expected result is : (If user enter this keyword : 'patel dha' or 'patel dha')

我的预期结果是:(如果用户输入此关键字:'patel dha'或'patel dha')

UserID  FirstName  LastName  FullName   
------  ---------  --------  --------
2   Dharmesh   Patel     Dharmesh Patel 
4   Patel      Ankita    Patel Ankita
3   Patel      Pranay    Patel  Pranay
1   Manish     Patel     Manish Patel

if user search another way then my expected result is : (If user enter this keyword : 'patel dha pr' or 'patel dha pran')

如果用户搜索另一种方式,那么我的预期结果是:(如果用户输入此关键字:'patel dha pr'或'patel dha pran')

UserID  FirstName  LastName  FullName   
------  ---------  --------  --------
2   Dharmesh   Patel     Dharmesh Patel 
3   Patel      Pranay    Patel  Pranay
4   Patel      Ankita    Patel Ankita
1   Manish     Patel     Manish Patel

Order changed. (because most matching words found)

订单已更改。 (因为找到了大多数匹配单词)

Can we use here regular expression? or may be another way to do this?

我们可以在这里使用正则表达式或者可能是另一种方法吗?

thanks

1 个解决方案