SQL query statement wildcard character and access fuzzy query like solution _ Application Tips

Access wildcard characters and SQL Server wildcard comparisons
===================================================
The wildcard characters for the Access library are:
* match any number of characters
? match the characters of any single letter

The wildcard characters in SQL Server are:
% matches any number of characters
_ Matches a single character
Body
I am writing a page today, also very depressed, the table clearly has records, but in the ASP is not to search, the theory of SQL statements are as follows:
Select * from T_food Where t_food.name like ' * apple * '
To Google search found that the ASP in the fuzzy query to write:
Select * from T_food Where t_food.name like '%% apple% '

Must be "%" and two. Everybody pay more attention to it.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL query Statement wildcard problem

When you use SQL statements in Access to query data, you use the wildcard character * to query. Statement as follows:

Select * from normal where bookname like ' *h* '

Experiment in Access's SQL view without any problems, everything works fine. Then write the SQL statement to the C # program, the result of a query statement when the error jumped out, baffled by the solution. So find the Access Help file and find the following help:

////////////////////////////////////////////////////////////

Compares a string expression with a pattern in an SQL expression.

Grammar
Expression like "pattern"

The LIKE operator syntax contains the following sections:

Part of the description
Expression The SQL expression used in the WHERE clause.
string literal that is compared to expression.

Description
You can use the LIKE operator to find the field value that matches the pattern you specify. For pattern, you can specify a full value (such as like "Smith"), or you can use wildcards to find values within a range (such as like "sm*").

In an expression, you can use the LIKE operator to compare field values with strings. For example, if you enter like "c*" in an SQL query, the query returns all field values beginning with the letter C. In a parameter query, you can prompt the user to type a pattern to search for.

The following example returns data that begins with the letter P and is followed by any letters between A and F and three digits:

Like "p[a-f]###"

The following table shows how you can test expressions of different patterns through like.

Match type
Pattern matching
(returns True) does not match
(returns False)
Multiple characters a*a AA, ABa, Abbba ABC
*ab* ABC, AABB, Xab Azb , BAC
Special Character A
A a*a AAA
Multiple characters ab* ABCDEFG, ABC cab, AAB
Single character A?a aaa, a3a, ABa Abbba
Single digit a#a a0a, A1A, A2A aaa, a10a
Character range [A-z] f, p, J 2, &
Outside the range [!a-z] 9,% B, a
Non-numeric values [!0-9] A, a, and, ~ 0, 1, 9
Compound value a[!b-m]# An9, az0, a99 ABC, aj0

Reference address:http://office.microsoft.com/zh-cn/assistance/HP010322532052.aspx

///////////////////////////////////////////////////////////

Help all write so, no problem ah, in the end the problem is where? More let me confused. Later asked a colleague said: "Your SQL statement is wrong, the wildcard should be used%, rather than *." But the help said is *, and I tried everything in access to normal, colleagues can not tell the reason, so continue to find help demand answers. The following information was found in another Help file:

///////////////////////////////////////////////////////////

The built-in pattern-matching method provides a common tool for string comparisons. The following table shows the wildcard characters that can be used with the LIKE operator, as well as the numbers and strings that match them.

Matches in the character expression in pattern
? Or _ (underline) any single character
* or% 0 or more characters
# any single digit (0-9)
[charlist] Any single character in the charlist.
[!charlist] is not a single character in the charlist.

You can use a set of one or more characters (charlist) enclosed in brackets ([]) to match any single character in expression, and charlist can contain most of the characters in the ANSI character set, including numbers. You can directly match a specific character by Furu the left parenthesis ([), Question mark (?), Number (#), and asterisk (*) in square brackets. The closing bracket cannot be used in a group to match itself, but it can be used as a single character outside the group.

In addition to the simple character characters enclosed in square brackets, charlist can delimit the upper and lower bounds of a range by using a hyphen (-). For example, when using [A-z] in pattern, a match can be achieved if the corresponding character in the expression contains any uppercase characters between a and Z ranges. You can include multiple scopes in square brackets without having to delimit the scope. For example, [a-za-z0-9] can match any alphanumeric character.

Note that the ANSI SQL wildcard character (%) and (_) only at Microsoft? The Jet 4.X version and Microsoft OLE DB Provider for Jet are valid. If used in Microsoft Access or DAO, they are treated as text.

Other important rules for pattern matching are as follows:

Using an exclamation point (!) at the beginning of the charlist will indicate that if any character other than charlist appears in expression, a match occurs. When it is used outside the square brackets, the exclamation point matches itself.
You can use a hyphenation symbol (-) for the beginning of the charlist (after the exclamation point) or the end to match itself. In any other location, the hyphenation symbol identifies an ANSI character range.
When a range of characters is specified, the characters must appear in ascending order (A-Z or 0-100). [A-z] is a valid pattern, [z-a] is an invalid mode.
Ignore character order []; it is treated as a 0-length character ("").
Reference address:http://office.microsoft.com/zh-cn/assistance/HP010322842052.aspx

///////////////////////////////////////////////////////////////

At this point, the reason is finally found, because I use wildcards in Access * All normal, replace the% can not be successful. C # is only supported% wildcard characters, and the * will be wrong! Does this question count as a compatibility issue?

Wildcard characters:

Wildcard Description Example
% contains any string of 0 or more characters. Where title like '%computer% ' will find all titles that contain the word computer anywhere in the title.
_ (underline) any single character. Where au_fname like ' _ean ' will look for all 4-letter names (Dean, Sean, etc.) ending in EAN.
[] Any single character in the specified range ([a-f]) or collection ([abcdef]). Where au_lname like ' [C-p]arsen ' will look for author surnames that begin with Arsen ending with any single character between C and P, for example, Carsen, Larsen, Karsen, and so on.
[^] Any single character that does not belong to the specified range ([a-f]) or set ([abcdef]). Where au_lname like ' de[^l]% ' will look for the last names of all authors who started with de and whose letter is not followed by L.

Use wildcard characters as text

You can use a wildcard pattern matching string as a literal string by placing the wildcard characters in parentheses. The following table shows examples of using the LIKE keyword and [] wildcard characters.

Symbolic meaning
Like ' 5[%] ' 5%
Like ' [_]n ' _n
Like ' [A-CDF] ' A, B, C, D or F
Like ' [-ACDF] '-, A, C, D, or F
Like ' [[] ' [
Like '] '
Like ' abc[_]d% ' abc_d and Abc_de
Like ' abc[def] ' abcd, ABCE and ABCF

Pattern matching using the ESCAPE clause

You can search for a string that contains one or more special wildcard characters. For example, a discounts table in the customers database might store a percent sign (%) The discount value. To search for a percent semicolon as a character instead of a wildcard, you must provide an escape keyword and an escape character. For example, a sample database contains a column named comment containing text 30%. To search for any row that contains string 30% anywhere in the comment column, specify a WHERE clause consisting of the where comment like '%30!%% '! '. If you do not specify escape and escape characters, SQL Server returns all rows containing the string 30.

The following example shows how to search for a string in the notes column of the Pubs database titles table "50% or more copies are purchased":

Select Notes from titles Where notes like "50%% off" or more copies are purchased ' ESCAPE '%