Go SQL Fuzzy Query

In the database query, there is a complete query and fuzzy query points.

General Fuzzy query statements are as follows:

SELECT field from table WHERE a field like condition

With regard to the conditions, SQL provides four matching modes:

1,%: Represents any 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 __ ';
Just find out "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.

such as SELECT * from [user] WHERE u_name like ' [Zhang Li Wang] three '
Will find "Zhang San", "Lie Triple", "Wang San" (not "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 "Old 1", "Old 2" 、......、 "Old 9";

4,[^]: Represents a single character that is not listed within the 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 "[]" in the normal query. 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, and it is important to note that when a database is connected to a Web page using this type of query statement:

such as SELECT * from user Where name is like ' old [^1-4] '; above "old [^1-4]" ' "is to have single quotation marks, do not forget, I often forget!

Access

In the recent writing Web program used the fuzzy query access, in the Acces write code how can not find records, later only up the original acess and SQL Server fuzzy query is a special
Condition: Lookup Table A in the Name field includes "B" in the record
Code in Access:

1 Select * from a where name like ' *b* ' SQL Server Query Analyzer code
Select * from a where name like '%b% ' you will find that access can find relevant records, but the ' * ' must be '% ' will not be found, because the fuzzy query of Access is '? ', ' * '
Not the same as SQL Server
The above is only the code in the database, if you want to write in the program can not be used. * ', or to use '% '
Program:
Strsql= "SELECT * from a where name likes '%b% '" so if there are friends and I like to first in the database code testing, it is necessary to pay attention to!!

----------------------------------------------------------------------------------------------------------

SQL Fuzzy Query, using the LIKE keyword, plus the wildcard character in SQL, please refer to the following:  
1, like ' mc% ' will search for all strings that begin with the letter Mc (such as McBadden).  
2, like '%inger ', searches for all strings that end with the letter inger (such as Ringer, Stringer).  
3, like '%en% ' will search for all strings containing the letter en in any location (such as Bennet, Green, McBadden).  
4, like ' _heryl ' will search for all six-letter names (such as Cheryl, Sheryl) that end with the letter heryl.  
5, like ' [Ck]ars[eo]n ', will search for the following strings: Carsen, Karsen, Carson, and Karson (such as Carson).  
6, like ' [M-z]inger ' will search for all names (such as Ringer) that end with a string inger, starting with any single letter from M to Z.  
7, like ' m[^c]% ', starts the search with the letter M, and the second letter is not all names of C (such as Macfeather). &NBSP
------------------------------------------------- 
The following query string is what I wrote before, according to the variable Zipcode_key in the ZIP code table ZipCode query the corresponding data, this sentence is the judgment variable Zipcode_key is not a number when the query statement, with% to match any length of the string, from the table address, city, province three columns query contains the key words of all data items, and by province, city, address sorting. This is a simple example, so you can write more complex query statements as long as you understand the method.  

sql = "SELECT * from ZipCode where (address like '%" & zipcode_key & "% ') or (city like '%" & ZIPC Ode_key & "%") or (province like '% ' & zipcode_key & "% ') Order by province,city,address

Examples of using fuzzy queries in stored procedures: SELECT * from Questions where qtitle like '% [' + @KeyWord + ']% ' and isfinish = @IsFinsih
The paired square brackets in a statement are key to the writing format. Original Address http://www.cnblogs.com/GT_Andy/archive/2009/12/25/1921914.html

Go SQL Fuzzy Query