SQL fuzzy query example explanation

Preface

When it comes to fuzzy queries in SQL, the first thing that comes to mind should be the like keyword.

When we need to query data containing a specific field, we often use the '%keyword%' query method. For example:

SELECT ... FROM 表名 WHERE 字段名 LIKE '%关键字%'

This should be regarded as a typical "contains XXX" method, but what if we need to query data that contains specific characters in a field?

For example, I have a contact data table ConnectName, which has a field for recording names. I want to get the contact information of people named Xiaolan and Haiyuan. Under normal circumstances, the first thing we can think of is:

SELECT * FROM ConnectName 
WHERE
  name = '小兰'
  OR name = '灰原'

This approach can achieve this purpose. If at this time, I suddenly want to check another person, such as "Conan", then we have to modify the SQL structure and add a Where conditional sentence:

SELECT * FROM ConnectName 
WHERE
  name = '小兰'
  OR name = '灰原'
  OR name = '柯南'

We know that the OR condition query itself is inefficient, and the structural change statement is a little more troublesome to implement in MyBatis (of course it can also be implemented, just traverse the inserted fields).

Can it be simpler? Can I put all the keywords together and use only one Where condition to achieve it?

CHARINDEX appears

At this time, we can use the CHARINDEX keyword. CHARINDEX can return a certain field that appears in a string of text The position is similar to the usage of String's indexOf. Without further ado, let's give a chestnut:

CHARINDEX('李白','曹操很帅') =0

In the chestnut above, because Cao Cao is very handsome, the key to Li Bai is not included. word, so it cannot be found, and returns 0.

CHARINDEX('李白','李白很帅') =1

The same chestnut, because it contains the Li Bai keyword, will return the index of the first word where the keyword is located , so return 1.

After understanding the usage, we can use the CHARINDEX keyword to optimize our SQL statement:

SELECT * FROM ConnectName 
WHERE
  CHARINDEX(name ,'小兰灰原柯南')>0

If the name field The corresponding name appears in 'Conan Xiaolan Haibara', then the CHARINDEX function will return greater than 1, and we can get the data we want (the three of them can also play happily together^-^)

The corresponding mybatis implementation is also relatively simple

SELECT * FROM ConnectName 
WHERE
  <!--[CDATA[ AND CHARINDEX(name ,#{传入的参数}) --> 0 ]]>

If we want to add a new person later, such as Mouri Kogoro, we only need to pass in the parameters Just add 'Xiaoran Haibara Conan Mouri Kogoro' to it. Isn't it much simpler?

The above is the detailed content of SQL fuzzy query example explanation. For more information, please follow other related articles on the PHP Chinese website!