SQL fuzzy query

Complete and fuzzy queries are available for database queries.

For more information about SQL fuzzy queries, see the following:

1. LIKE 'mc % 'searches all strings starting with Mc (for example, McBadden ).

2. LIKE '% inger' searches all strings ending with the letter inger (such as Ringer and Stringer ).

3. LIKE '% en %' searches for all strings (such as Bennet, Green, and McBadden) containing the letter en at any position ).

4. LIKE '_ heryl' searches for names (such as Cheryl and Sheryl) of all six letters ending with 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' searches for all names (such as Ringer) ending with string inger, starting with any single letter from M to Z ).

7. LIKE 'M [^ c] %' searches for all names starting with M, and the second letter is not c (such as MacFeather ).

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

A complete example is required. The following query string is previously written. The zipcode_key variable is used to query the corresponding data in zipcode of the zip code table. The query statement is used to determine that the zipcode_key variable is not a number, use % to match strings of any length, query all data items containing keywords from the address, city, and province columns in the table, and sort by province, city, and address. This example is relatively simple. 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"

Details:

General fuzzy query statement:

SELECT field FROM table WHERE a field Like Condition

SQL provides four matching modes for conditions:

1, %: represents any 0 or multiple characters. It can match any type and length of characters. In some cases, if it is Chinese, use two percent signs (%.

For example, SELECT * FROM [user] WHERE u_name LIKE '% 3%'

We will find all records with "3", such as "3 Zhang", "3 Zhang", "3 Zhang Mao", and "3 Tang sanzang.

In addition, if you need to find records with "three" and "cat" in u_name, use the and condition.

SELECT * FROM [user] WHERE u_name LIKE '% 3%' AND u_name LIKE '% cat %'

If SELECT * FROM [user] WHERE u_name LIKE '% 3% cat %' is used'

Although three-legged cats can be searched, three-legged cats cannot be searched ".

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

For example, SELECT * FROM [user] WHERE u_name LIKE '_ 3 _'

Only find that the u_name such as "Tang sanzang" is three characters and the middle word is "three;

For example, SELECT * FROM [user] WHERE u_name LIKE 'three __';

Find out that the name of the "three-legged cat" is three characters and the first word is "three;

3, []: represents one of the characters listed in brackets (similar to a regular expression ). Specifies a character, string, or range. The matching object must be one of them.

For example, SELECT * FROM [user] WHERE u_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 u_name LIKE 'Old [1-9]'

Will find "Old 1", "old 2 ",...... , "Old 9 ";

4, [^]: represents a single character 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.

For example, SELECT * FROM [user] WHERE u_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 u_name LIKE 'Old [^ 1-4] ';

From "Old 1" to "old 4", search for "old 5", "old 6 ",......

5. When the query content contains wildcards

The special characters "%", "_", and "[" cannot be properly queried due to wildcards, when special characters are included in "[]", they can be queried normally. Then, write the following functions:

Function sqlencode (str)

Str = replace (str, "[", "[]") 'must be at the beginning

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

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

Sqlencode = str

End function

You can use this function to process the string to be queried before the query, and pay attention to the following when connecting to the database on the web page to use such query statements:

For example, Select * FROM user Where name LIKE 'Old [^ 1-4] '; the old [^ 1-4]' in the preceding example must have single quotation marks. Don't forget, I often forget!

Access

Recently, Access fuzzy queries were used when writing Web programs, and no records could be found when writing code in Acces. It was only then that the fuzzy queries of Acess and SqlServer were special.

Condition: Search for the "B" value in the Name field of Table.

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 the relevant records in Access, but you cannot find the '*' which must be '%, the reason is that the Access fuzzy query is '? ','*'

Different from SQL server

The above is only the code in the database. If you want to write it in the program, you cannot use. '*', or '%'

Program:

StrSql = "select * from a where name like '% B %'". So if a friend and I like to test code in the database first, pay attention to it !!

Bytes ----------------------------------------------------------------------------------------------------------

For SQL fuzzy query, use the like keyword and add the wildcards in SQL, refer to the following:

1. LIKE 'mc % 'searches all strings starting with Mc (for example, McBadden ).

2. LIKE '% inger' searches all strings ending with the letter inger (such as Ringer and Stringer ).

3. LIKE '% en %' searches for all strings (such as Bennet, Green, and McBadden) containing the letter en at any position ).

4. LIKE '_ heryl' searches for names (such as Cheryl and Sheryl) of all six letters ending with 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' searches for all names (such as Ringer) ending with string inger, starting with any single letter from M to Z ).

7. LIKE 'M [^ c] %' searches for all names starting with M, and the second letter is not c (such as MacFeather ).

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

The following query string was previously written. The zipcode_key variable is used to query the corresponding data in zipcode of the zip code table. This is the query statement used to judge that the zipcode_key variable is not a number, use % to match strings of any length, query all data items containing keywords from the address, city, and province columns in the table, and sort by province, city, and address. This example is relatively simple. 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

Example of fuzzy query used in stored procedures:

SELECT * FROM Questions where QTitle like '% [' + @ KeyWord + '] %' and IsFinish = @ IsFinsih

Square brackets in a statement are the key to the writing format.