About SQL fuzzy queries

The general fuzzy query statement is as follows:

SELECT  field  FROM  table  WHERE  A certain field  Like  conditions 

In terms of conditions in , SQL provides four matching patterns:

1, % : represents 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 '% 3% '

We will find out the records of u_name as "zhang SAN", "zhang MAO SAN", "three-legged cat", "tang sanzang" and so on.

Also, if you need to find a record of both "three" and "cat" in u_name, use and condition
SELECT * FROM [user] WHERE u_name LIKE '% 3% 'AND u_name LIKE '% cat %'

If you use SELECT * FROM [user] WHERE u_LIKE '% 3% cat %'
Although can search out "three legged cat", but can not search out the eligible "three cats".

2, _ : represents any single character. Matches a single arbitrary character, which is often used to limit the character length of an expression:

For example, SELECT * FROM [user] WHERE u_name LIKE '_ 3 _'
Only find out the "tang sanzang" such u_name for three words and a middle word is "three";

SELECT * FROM [user] WHERE u_name LIKE 'three!
Only find the word "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). Specifies a character, string, or scope that requires the matched object to be any of them.

For example, SELECT * FROM [user] WHERE u_name LIKE '[zhang li wang] three '
"Zhang SAN", "li SAN" and "wang SAN" (instead of "zhang li wang SAN") will be found;

If [] contains a series of characters (01234, abcde, etc.), it can be abbreviated 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 in the bracket. It has the same value 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] three '
Will find out the surname "zhang", "li", "wang" "zhao three", "sun three" and so on;

SELECT * FROM [user] WHERE u_name LIKE 'old [^1-4]';
Will exclude "old 1" to "old 4", look for "old 5", "old 6"...

5.

when the query contains wildcards

Because of the wildcard character, the statement that we query the special character "%", "_", "[" cannot be implemented normally, but the special character" [] "can be surrounded by the normal query, so we write the following function:

function sqlencode(str)
str=replace(str,"[","[[]") ' This sentence must come first 
str=replace(str,"_","[_]")
str=replace(str,"%","[%]")
sqlencode=str
end function

Just run the pending string through the function before the query, and note that

is used when connecting to the database on a web page

Select * FROM user Where name LIKE 'old [^1-4]'; The top 'old' [^1-4] 'is to have single quotes, don't forget, I often forget!

access

Recently, when writing Web program, I used Access's fuzzy query. However, I could not find the record when writing code in Acces. Later, I got up and found that Acess and SqlServer's fuzzy query have special
Condition: the Name field of the lookup table A contains an
for "B" Code in Access :

1 Select * from a where like '*b*'Sql Server query analyzer code
Select * from a where name like '%b%' you will find relevant records in Access, but you will not find them if the '*' must be '%' because Access's fuzzy query is '? ', '*'

is not the same as Sql server The above is only in the database code, if you want to write in the program can not use.'*', or to use '%'
Application:
strSql="select * from a name like '%b%'" so if there are friends like me like to test the code in the database first, that is to pay attention!!

SQL fuzzy query, using the like comparison keyword, plus the wildcard in SQL, please refer to the following:
1. LIKE'Mc%' searches for all strings beginning with the letter Mc (e.g. McBadden).
2. LIKE'%inger' searches for all strings ending with the letter inger (e.g. Ringer, Stringer).
3. LIKE'%en%' will search for all strings that contain the letter en anywhere (e.g. Bennet, Green, McBadden).
4. LIKE'_heryl' searches for all six letter names ending in the letter heryl (e.g. Cheryl, Sheryl).
5. LIKE'[CK]ars[eo]n' will search for the following strings: Carsen, Karsen, Carson and Karson (e.g. Carson).
6. LIKE'[M-Z]inger' searches for all names ending in the string inger and beginning with any single letter from M to Z (e.g. Ringer).
7. LIKE'M[^c]%' will be searched starting with the letter M, and the second letter is not all names of c (e.g. MacFeather).
-------------------------------------------------
The following query string is I wrote before, according to the variable zipcode_key zipcode in zip code table query in the corresponding data, it is a judgment variable zipcode_key for non-numeric query statements, use % to match any of the length of the string, address, city, province three columns from the table in the query keyword include all the data items, and according to the provincial, city and address. This is a simple example, as long as you understand the method you can write more complex queries.

sql = "select * from zipcode where (address like'%" & zipcode_key & "%') or (city like'%" & zipcode_key & "%') or (province like'%" & zipcode_key & "%') order by province,city,address

Example of using fuzzy queries in stored procedures:
SELECT * FROM Questions where QTitle like ' % [ '+ @KeyWord +' ] % ' and IsFinish = @IsFinsih

Pairs of square brackets in a statement are the key to formatting.