MySQL fuzzy query statement collates the collection

SQL fuzzy query statement

The general syntax for fuzzy statements is as follows:

SELECT field FROM table WHERE some field Like condition

With regard to conditions, SQL provides four matching patterns:

1.%: represents 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 (%%).

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. _: represents any single character. Matches a single arbitrary character, which is often used to limit the character length statement of an expression:

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".

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.

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.

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, "[", "[[]") this sentence must be preceded by str = replace (str, "_", "[_]") str=replace (str, "%", "[%]") sqlencode=strend function.

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

These are the details of the SQL fuzzy query sentence, if you have any additions, you can contact the editor.