Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to realize the fuzzy query of SQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Preface

When it comes to the fuzzy query of SQL, the first thing that comes to mind is the like keyword.

When we need to query data that contains a particular field, we often use the'% keyword% 'query. For example:

SELECT... FROM table name WHERE field name LIKE 'keyword'

This should be a typical way of "including XXX", but what if we need to query the data in which the field is contained in a particular character?

For example, I have a contact data sheet, ConnectName, with a field for recording the name name. I want to get the contact information of the person named Xiaolan and Gray Hara. Under normal circumstances, the first thing we can think of is:

SELECT * FROM ConnectName WHERE name = 'Xiaolan' OR name = 'Huiyuan'

This can be achieved by doing so. If at this point, I suddenly want to check another person, such as "Conan", then we have to modify the structure of SQL and add a Where conditional clause:

SELECT * FROM ConnectName WHERE name = 'Xiaolan' OR name = 'Asahara' OR name = 'Conan'

We know that OR conditional queries themselves are inefficient, and structurally altered statements are slightly more cumbersome to implement in MyBatis (of course, it's also possible to traverse insert fields).

Could you make it easier? Can I put all the keywords together and use only one Where condition to implement it?

CHARINDEX debut

At this point, we can use the CHARINDEX keyword, and CHARINDEX can return the position of a field in a string of text, similar to the indexOf usage of String. Without much nonsense, let's take a chestnut:

CHARINDEX ('Li Bai', 'Cao Cao is handsome') = 0

In the chestnut above, because Cao Cao is very handsome does not include the Li Bai keyword, so can not find, return 0. 5.

CHARINDEX ('Li Bai','Li Bai is handsome') = 1

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

Now that we know how to use it, we can use the CHARINDEX keyword to optimize our SQL statement:

SELECT * FROM ConnectName WHERE CHARINDEX (name, 'Xiaolan Grey original Conan') > 0

If the name corresponding to the name field appears in 'Xiaolan Gray original Conan', then the CHARINDEX function will return greater than 1 and we can get the data we want (the three of them can also have a good time together ^-^)

The corresponding mybatis implementation is also relatively simple.

SELECT * FROM ConnectName WHERE 0]] >

If we want to add a new person at a later stage, such as Richard Moore, we just need to add 'Xiaolan Gray original Conan Richard Moore' to the passed parameters.

This is the end of the content of "how to realize the fuzzy query of SQL". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.