When executing a database query, there is a complete query and a fuzzy query.
The general fuzzy statement is as follows:
SELECT field from table WHERE a field like condition
with regard to the conditions, SQL provides four matching modes:
1,%: denotes any 0 or more characters . Can match any type and length of characters, 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.
In addition, if you need to find the u_name in the "three" and "Cat" record, please apply and conditions
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 qualify the expression's character-length statement:
e.g. select * FROM [user] WHERE u_name like ' _ three _ '
Just find out" Don Sanzang "so U_name is three words and the middle one word is" three ";
again such as SELECT * from [user] WHERE u_name like ' three __ ';
only find" three-legged cat "this name is three characters and the first word is" three ";
3, []: Represents one (similar to regular expression) in the characters listed in parentheses. Specifies a character, string, or range that requires matching objects to be any of them.
e.g. select * FROM [user] WHERE u_ Name like ' [Zhang Li Wang] Three '
will find "Zhang San", "Lie triple "," Wang San "(not" Zhangli Kang ");
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 in parentheses . The value is the same as [], but it requires that the matched object be any character other than the specified character.
such as SELECT * from [user] WHERE u_name like ' [^ Zhang Li Wang] three '
Will find the surname "Zhang", "Li", "Wang" "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" 、......
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 "[]" can be queried normally . Thus we write the following function:
function Sqlencode (str)
str=replace (str, "[", "[[]") ' This sentence must be in the top
str=replace (str, "_", "[_]")
str=replace (str, "%", "[%]")
Sqlencode=str
End Function
The unknown origin string is processed by the function before the query.
Four matching patterns of common fuzzy queries in SQL