The syntax of SQL fuzzy query is
"Select column from table where column like 'pattern ';".
SQL provides four matching modes:
1.% indicates any 0 or multiple characters. Statement:
Select * from user where name like '% 3% ';
We will find all the "three" names for "Michael", "three-legged cats", and "Tang sanzang;
2. _ represents any single character. Statement:
Select * from user where name like '_ 3 _';
Only find that the name of "Tang sanzang" is three characters and the middle word is "three;
Select * from user where name like 'three __';
Find out that the name of the "three-legged cat" is three characters and the first word is "three;
3. [] indicates one of the characters listed in brackets (similar to regular expressions ). Statement:
Select * from user where name like '[Zhang Li Wang] 3 ';
We will find "Zhang San", "Li San", and "Wang San" (rather than "Zhang Li Wang San ");
For example, if [] contains a series of characters (01234, ABCDE, etc.), it can be slightly written as "0-4" or "a-e"
Select * from user where name like 'Old [1-9] ';
Will find "Old 1", "old 2 ",...... , "Old 9 ";
If you want to find the "-" character, put it in the first place: 'zhang San [-1-9] ';
4. [^] indicates a single character not listed in parentheses. Statement:
Select * from user where name like '[^ Zhang Li Wang] 3 ';
We will find "Zhao San" and "Sun San" without the surname "Zhang", "Li", and "Wang;
Select * from user where name like 'Old [^ 1-4] ';
From "Old 1" to "old 4", find "old 5", "old 6 ",...... And "old 9 ".
! The last is the focus!
Because of the wildcard characters, the statements for querying special characters "%", "_", "[", and "';" cannot be implemented normally, when special characters are included in "[]", they can be queried normally. Then, write the following functions:
Function sqlencode (STR)
STR = Replace (STR ,"';","';';")
STR = Replace (STR, "[", "[[]") '; this sentence must be the first
STR = Replace (STR, "_", "[_]")
STR = Replace (STR, "%", "[%]")
Sqlencode = Str
End Function
The string to be queried can be processed by this function before query.