SQL Fuzzy Query

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

SQL Fuzzy query, using the like comparison word, plus the wildcard characters in SQL, please refer to the following:

1, like ' mc% ' will search all strings (such as McBadden) that begin with the letter Mc.

2, like '%inger ' will search all strings ending with the letter inger (such as Ringer, Stringer).

3, like '%en% ' will search all the strings containing the letter en in any position (such as Bennet, Green, McBadden).

4. Like ' _heryl ' will search for all six-letter names (such as Cheryl, Sheryl) ending with the letter heryl.

5, like ' [Ck]ars[eo]n ' will search 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 begin with the end of the string inger, starting with any single letter from M to Z.

7, like ' m[^c]% ' will search begins with the letter M, and the second letter is not all the names of C (such as Macfeather).

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

Oh, to complete the example AH. The following query string is I wrote before, according to the variable Zipcode_key in the Postcode table ZipCode query the corresponding data, this sentence is to judge the variable Zipcode_key as a non-numeric query, with% to match any length of the string, from the table address, city, The three columns of the province query all data items that contain keywords, sorted by province, city, and address. This is a simple example, as long as you understand the method you can write more complex query statements.

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

Detailed Introduction:

General Fuzzy query statement:

SELECT field from table WHERE a field like condition

With regard to conditions, SQL provides four matching modes:

1,%: Represents any 0 or more characters. Can match any type and length of characters, in some cases in Chinese, please use two percent sign (%).

For example, SELECT * from [user] WHERE u_name like '% III '

will be u_name as "John", "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 in u_name that has "three" and "cat", use the and condition

SELECT * FROM [user] WHERE u_name like '% III ' and u_name like '% Cat '

If you use SELECT * from [user] WHERE u_name like '% cat% '

Although can search out "three-legged cat", but can not search out the conditions of the "cat three".

2,_: Represents any single character. Matches a single arbitrary character, which is used to restrict the character-length statements of an expression:

For example, SELECT * from [user] WHERE u_name like ' _ Three _ '

Only find "Tang Sanzang" so U_name is three words and the middle one word is "three";

Another example is SELECT * from [user] WHERE u_name like ' three __ ';

Only find "three-legged cat" so 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, which requires that the matched objects be any of them.

For example, SELECT * from [user] WHERE u_name like ' [Zhang Li Wang] three '

Will find "John", "Lie Triple Systems", "Wang San" (rather than "Zhangli Kang");

If [] There are 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 and [] are the same, 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 not the surname "Zhang", "Li", "Wang" "Zhao Three", "Sun San" 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, the query content contains the characters

Because of the wildcard character, we query special characters "%", "_", "[" statements can not be implemented normally, and special characters in "[]" around the normal query. Thus we write the following function:

function Sqlencode (str)

Str=replace (str, "[", "[[]") ' This sentence must be at the front

Str=replace (str, "_", "[_]")

Str=replace (str, "%", "[%]")

Sqlencode=str

End Function

The query string is processed first by this function before querying, and you should note when you use this type of query to connect to the database on a Web page:

For example, select * from user Where name is like ' old [^1-4] '; the old [^1-4] "'" is to have single quotes, don't forget, I often forget!

Access

In the recent writing Web program used in access to the fuzzy query, in acces write code how can not find records, then get up the original acess and SQL Server fuzzy query is a special

Condition: Find a note in the Name field of table A that includes "B"

Code in Access:

1 Select * from a where name like ' *b* ' SQL Server Query Analyzer code

Select * from a where name like '%b% ' then you will find that access can find the relevant records, but the ' * ' must be '% ' can not be found, because Access's fuzzy query is '? ', ' * '

Not the same as SQL Server

The above is just 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 like '%b% '" so if you have friends and I love the first in the database code testing, it is necessary to pay attention to!!

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

SQL fuzzy queries, using the like comparison keyword, plus the wildcard characters in SQL, refer to the following:

1, like ' mc% ' will search all strings (such as McBadden) that begin with the letter Mc.

2, like '%inger ' will search all strings ending with the letter inger (such as Ringer, Stringer).

3, like '%en% ' will search all the strings containing the letter en in any position (such as Bennet, Green, McBadden).

4. Like ' _heryl ' will search for all six-letter names (such as Cheryl, Sheryl) ending with the letter heryl.

5, like ' [Ck]ars[eo]n ' will search 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 begin with the end of the string inger, starting with any single letter from M to Z.

7, like ' m[^c]% ' will search begins with the letter M, and the second letter is not all the names of C (such as Macfeather).

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

The following query string is I wrote before, according to the variable Zipcode_key in the Postcode table ZipCode query the corresponding data, this sentence is to judge the variable Zipcode_key as a non-numeric query, with% to match any length of the string, from the table address, city, The three columns of the province query all data items that contain keywords, sorted by province, city, and address. This is a simple example, as long as you understand the method you can write more complex query statements.

sql = "SELECT * from ZipCode where" [address like '% ' & zipcode_key & '% '] or (city like '% & Zipcode_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

A pair of square brackets in a statement is the key to the writing format.