Introduction to four usages of fuzzy query in Mysql _mysql

Here are four ways to use fuzzy queries in MySQL:

1,%: Represents any 0 or more characters. Can match any type and length of characters, in some cases in Chinese, please use two percent sign (%).
For example, SELECT * from [user] WHERE u_name like '% III '
will be u_name as "John", "Zhang Cat Three", "three-legged Cat", "Tang Sanzang" and so on Have "three" records all find out.
Also, if you need to find a record in u_name that has "three" and "cat", use the and condition
SELECT * FROM [user] WHERE u_name like '% III ' and u_name like '% Cat '
If you use SELECT * from [user] WHERE u_name like '% cat% '

Although can search out "three-legged cat", but can not search out the conditions of the "cat three".

2,_: Represents any single character. Matches a single arbitrary character, which is used to restrict the character-length statements of an expression:
For example, SELECT * from [user] WHERE u_name like ' _ Three _ '
Only find "Tang Sanzang" so U_name is three words and the middle one word is "three";

Another example is SELECT * from [user] WHERE u_name like ' three __ '; Only find "three-legged cat" so name is three words and the first word is "three";

3,[]: Represents one of the characters listed in parentheses (similar to a regular expression). Specifies a character, string, or range, which requires that the matched objects be any of them.
For example, select * from [user] WHERE u_name like ' [Zhang Li Wang] III ' will find "John", "Lie Triple Systems", "Wang San" (rather than "Zhangli Kang");
If [] There are a series of characters (01234, ABCDE, etc.) can be slightly written as "0-4", "A-E"
SELECT * FROM [user] WHERE u_name like ' old [1-9] ' will find "Old 1", "Old 2" 、......、 "Old 9";

4,[^]: Represents a single character that is not listed within the parentheses. The value and [] are the same, but it requires that the matched object be any character other than the specified character.
For example, select * from [user] WHERE u_name like ' [^ Zhang Li Wang] Three ' will find out not the surname "Zhang", "Li", "Wang" "Zhao Three", "Sun San" and so on;
SELECT * FROM [user] WHERE u_name like ' old [^1-4] '; Will exclude "old 1" to "Old 4", Looking for "old 5", "Old 6" 、......

5, the query content contains the characters
Because of the wildcard character, we query special characters "%", "_", "[" statements can not be implemented normally, and special characters in "[]" around the normal query. Thus we write the following function:
function Sqlencode (str) str=replace (str, "';", "'; ';")
Str=replace (str, "[", "[[]") '; This sentence must be in the first str=replace (str, "_", "[_]") Str=replace (str, "%", "[%]") Sqlencode=str end function