The usage of SQL Fuzzy query

This article mainly introduces "the usage of SQL fuzzy query". In the daily operation, I believe that many people have doubts about the usage of SQL fuzzy query. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "the usage of SQL fuzzy query". Next, please follow the editor to study!

= re-edit the text as follows:

When querying the database, it can be divided into complete query and fuzzy query.

General fuzzy statements are as follows:

SELECT field FROM table WHERE some field Like condition

With regard to conditions, SQL provides four matching patterns:

1%: indicates any 0 or more characters. Characters of any type and length can be matched. In some cases, if it is in Chinese, please use two percent signs (%%).

Such as SELECT * FROM [user] WHERE u_name LIKE'% 3%'

U_name will be identified as "Zhang San", "Zhang Mao San", "three-legged cat", "Tang Sanzang" and other records of "three".

In addition, if you need to find out that there are both "three" and "cat" records in u_name, use the and condition

SELECT * FROM [user] WHERE u_name LIKE'% 3% 'AND u_name LIKE'% cat%'

If you use SELECT * FROM [user] WHERE u_name LIKE'% 3% cat%'

Although you can search for "three-legged cat", you can't find a qualified "Zhang Cat three".

2. Magic: represents any single character. Matches a single arbitrary character, which is often used to limit the character length statement of an expression:

For example, SELECT * FROM [user] WHERE u_name LIKE'_ three _'

Only find out "Tang Sanzang" so that the u_name is three words and the middle word is "three".

Another example is SELECT * FROM [user] WHERE u_name LIKE 'San _'

Just find out "three-legged cat" so that name is three words and the first word is "three".

3, []: represents one of the characters listed in parentheses (similar to a regular expression). Specify a character, string, or range that requires the matching object to be any one of them.

For example, SELECT * FROM [user] WHERE u_name LIKE'[Wang Li] San'

Will find "Zhang San", "Li San" and "Wang San" (instead of "Zhang Li Wang San")

If there are a series of characters (01234, abcde, etc.) in [], they can be abbreviated as "0-4" or "Amure".

SELECT * FROM [user] WHERE u_name LIKE 'Old [1-9]'

Will find out "old 1", "old 2", …... , "Lao 9"

4, [^]: represents a single character that is not listed in parentheses. Its value is the same as [], but it requires that the matching object be any character other than the specified character.

For example, SELECT * FROM [user] WHERE u_name LIKE'[Zhang Li Wang] San'

Will find out "Zhao San" and "Sun San" who are not surnamed "Zhang", "Li", "Wang", etc.

SELECT * FROM [user] WHERE u_name LIKE 'Old [^ 1-4]'

Will rule out "old 1" to "old 4", look for "old 5", "old 6", …...

5. When the query contains wildcards

Due to wildcards, we can not query the special characters "%", "_" and "[" normally, but we can query the special characters with "[]". Based on this, we write the following functions:

Function sqlencode (str)

Str=replace (str, "[", "[[]") 'must come first.

Str=replace (str, "_", "[_]")

Str=replace (str, "%", "[%]")

Sqlencode=str

End function

The string to be checked can be processed by this function before the query.

At this point, the study of "the use of SQL fuzzy query" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!