1,%: Represents any of 0 or more characters. Can match any type and length of the character, in some cases, if Chinese, please use two percent sign (%).
For example SELECT * from [user] WHERE u_name like '% three '
will be u_name for "Zhang San", "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 of "three" and "cat" in U_name, use the and condition
SELECT * FROM [user] WHERE u_name like '% three ' and u_name like '% cat% '
if using SELECT * from [user] WHERE u_name like '% cat% '
Although can search out "three feet cat", but can not search out the eligible "Zhang Cat three".
2,_: Represents any single character. Matches a single arbitrary character, which is commonly used to restrict the expression's character-length statement:
For example SELECT * from [user] WHERE u_name like ' _ three _ '
only Find "Tang Sanzang" so u_name for three words and the middle of a word is "three";
Another example is select * FROM [user] WHERE u_name like ' three __ '; only find "three-legged cat" this 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 that requires matching objects to be any of them.
For example SELECT * from [user] WHERE u_name like ' [Zhang Li Wang] Three ' will find "Zhang San", "Lie Triple", "Wang San" (instead of "Zhangli Kang");
such as [] 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 out "old 1", "Old 2" 、......、 "Old 9";
4,[^]: Represents a single character that is not listed in parentheses. The value is the same as [], 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 the surname "Zhang", "Li", "King" of the "Zhao three", "Magozo" 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" 、......Red section is not allowed, is used toregexp time instead of like.
5, when the query contains a wildcard character
because of the wildcard character, which causes us to query the special characters "%", "_", "[" the statement can not be implemented normally, and the special characters with "[]" in 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
Null
Four ways to use MySQL fuzzy search