The SQL query field is included in the statement. The SQL field contains the statement.

The SQL query field is included in the statement. The SQL field contains the statement.

Preface

When it comes to SQL fuzzy queries, the first thought should be the like keyword.

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

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

This can be regarded as a typical "include XXX" method, but what if we need to query data where a field is contained in a specific character?

For example, I have a contact data table ConnectName, where a field is used to record the name. I want to obtain the contact information of a person named Alan and gray. Under normal circumstances, the first thing we can think of is:

SELECT * FROM ConnectName WHERE name = 'xiaoland' OR name = 'Gray out'

This can be achieved. If at this time, I suddenly thought about another person, for example, "Conan", then we should modify the SQL structure and add a Where clause:

SELECT * FROM ConnectName WHERE name = 'xiaoland' OR name = 'Gray origin' OR name = 'conan'

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

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

CHARINDEX debut

At this time, we can use the CHARINDEX keyword, CHARINDEX can return the position of a field in a String of text, similar to the String indexOf usage, not much nonsense, let's give a chestnut:

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

In the chestnut above, because Cao is handsome and does not contain the Li Bai keyword, he cannot find it and returns 0.

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

Because the Li Bai keyword is included, the index of the first word at the beginning of the keyword is returned, so 1 is returned.

After learning how to use it, we can use the CHARINDEX keyword to optimize our SQL statement:

SELECT * FROM ConnectName where charindex (name, 'Little blue Gray Original Conan ')> 0

If the name corresponding to the name field appears in 'xiaolangyuan Conan ', the CHARINDEX function returns a value greater than 1, we can get the data we want (three of them can also have fun together ^-^)

The corresponding mybatis implementation is also relatively simple

SELECT * FROM ConnectName WHERE <! -- [CDATA [and charindex (name, # {input parameter}) --> 0]>

If we want to join a new person in the future, for example, Mao Lixiao and Wu Lang, we only need to add 'xiao lan Gray yuan ke Nan Mao Lixiao and Wu Lang 'to the input parameters. Is it much simpler?

The preceding section describes the SQL fields included in the query statement. I hope it will be helpful to you. If you have any questions, please leave a message. The editor will reply to you in time!