When I was writing a page today, I was also very depressed. There were records in the table, but I couldn't find them in ASP. It turned out to be caused by the different wildcard characters in the query statements of access and SQL.
Comparison of wildcard characters in ACCESS and wildcard characters in SQL SERVER
================================================== =
The wildcard characters for the ACCESS library are:
*matches any number of characters
?matches any single-letter character
Wildcard characters in SQL Server are:
% matches any number of characters
_ matches a single character
text
When I was writing a page today, I was also very depressed. There were records in the table, but I couldn't find them in ASP. The theoretical SQL statement is as follows:
Select * FROM t_food Where t_food.name like '*apple*'
Searching on GOOGLE, I found that the fuzzy query in ASP should be written like this:
Select * FROM t_food Where t_food.name like '%%apple%%'
It must be %, and there must be two. Please pay attention.
++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++
SQL query statement wildcard problem
When using SQL statements to query data in Access, the wildcard character * is used for querying. The statement is as follows:
Select * from normal where bookname like '*h*'
Tried it in Access's SQL view without any problems, everything works fine. So I wrote the SQL statement into the C# program, but as soon as the query statement was reached, an error occurred. I was puzzled. So I looked for the Access help file and found the following help:
///////////////////////////////////////////////////// //////////
Compares a string expression to a pattern in an SQL expression.
grammar
expression like pattern
The Like operator syntax consists of the following parts:
Partial description
expression The SQL expression used in the Where clause.
The string literal pattern is compared to expression.
illustrate
You can use the Like operator to find field values that match a specified pattern. For pattern, you can specify a complete value (for example, Like Smith), or you can use wildcards to find a range of values (for example, Like Sm*).
In expressions, you can use the Like operator to compare field values to strings. For example, if you enter Like C* in a SQL query, the query returns all field values that begin 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 letter from A to F and three numbers:
Like P[AF]###
The following table shows how to test different patterns of expressions through Like.
match type
pattern matching
(Return True) No match
(Return False)
Multiple characters a*a aa, aBa, aBBBa aBC
*ab* abc, AABB, Xab aZb, bac
Special character a
aa*a aaa
Multiple characters ab* abcdefg, abc cab, aab
Single character a?a aaa, a3a, aBa aBBBa
Single numbers a#a a0a, a1a, a2a aaa, a10a
Character range [az] f, p, j 2, &
Out of range [!az] 9, &, % b, a
Non-numeric values [!0-9] A, a, &, ~ 0, 1, 9
Composite value a[!bm]# An9, az0, a99 abc, aj0
Reference address: http://office.microsoft.com/zh-cn/assistance/HP010322532052.aspx
///////////////////////////////////////////////////// /////////
The help says this, and there are no problems. What is the problem? It confuses me even more. Later, I asked a colleague and said: Your SQL statement is wrong. The wildcard character should be % instead of *. But the help says *, and everything works fine when I test it in Access. My colleagues can’t explain why, so they continue to look for answers to help requests. The following information was found in another help file:
///////////////////////////////////////////////////// /////////
The built-in pattern matching methods provide a general tool for string comparison. The following table shows the wildcard characters that can be used with the Like operator, and the numbers and strings they match.
Characters in pattern Matches in expression
? or _ (underscore) any single character
* or % zero or more characters
# Any single number (0-9)
[charlist] Any single character in charlist.
[!charlist] Any single character not in charlist.
You can use a set of one or more characters (charlist) enclosed by brackets ([]) to match any single character in expression, and charlist can contain most characters in the ANSI character set, including numbers. Specific characters such as the left bracket ([), question mark (?), digit sign (#), and asterisk (*) can be matched directly against the symbols themselves by enclosing them in square brackets. The closing bracket cannot be used within a group to match itself, but it can be used as a single character outside the group.
In addition to a simple character list enclosed in square brackets, a charlist can have upper and lower range bounds separated by using a hyphen (-). For example, when using [AZ] in pattern, a match is achieved if the corresponding character in expression contains any uppercase character in the range A to Z. You can enclose multiple ranges in square brackets without delimiting the ranges. For example, [a-zA-Z0-9] matches any alphanumeric character.
Please note that the ANSI SQL wildcard characters (%) and (_) are valid only in Microsoft® Jet 4.X version and the Microsoft OLE DB Provider for Jet. 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 a charlist will indicate that a match will occur if any character outside of the charlist appears in expression. When used outside square brackets, the exclamation mark matches itself.
A hyphen (-) can be used at the beginning (after the exclamation mark) or at the end of a charlist to match itself. In any other position, the hyphen identifies a range of ANSI characters.
When a character range is specified, characters must appear in ascending order (AZ or 0-100). [AZ] is a valid mode, [ZA] is an invalid mode.
Character order [ ] is ignored; it is treated as a zero-length character ( ).
Reference address: http://office.microsoft.com/zh-cn/assistance/HP010322842052.aspx
///////////////////////////////////////////////////// //////////////
At this point, the reason has finally been found. Because I use the wildcard * in Access, everything works fine, but if I change it to %, it will not succeed. In C#, only the % wildcard is supported, and replacing it with * will cause an error! Is this issue considered a compatibility issue?
Wildcard:
Wildcard description example
% Any string containing zero or more characters. WHERE title LIKE '%computer%' will find all book titles containing the word computer anywhere in the title.
_ (underscore) Any single character. WHERE au_fname LIKE '_ean' will find all 4-letter names ending in ean (Dean, Sean, etc.).
[ ] Specifies any single character in a range ([af]) or set ([abcdef]). Where au_lname LIKE '[CP]arsen' will find author surnames ending with arsen and starting with any single character between C and P, for example, Carsen, Larsen, Karsen, etc.
[^] Any single character that does not belong to the specified range ([af]) or set ([abcdef]). Where au_lname LIKE 'de[^l]%' will find all author surnames that begin with de and are not followed by l.
Use wildcards as literals
You can use wildcard pattern matching strings as literal strings by enclosing the wildcard characters in parentheses. The following table shows an example of using the LIKE keyword and the [ ] wildcard character.
Symbol 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
Searches for strings that contain one or more special wildcard characters. For example, the discounts table in the customers database might store discount values with a percent sign (%). To search for the percent sign as a character rather than a wildcard character, you must provide the ESCAPE keyword and an escape character. For example, a sample database contains a column named comment that contains 30% text. To search for any rows that contain 30% of the string anywhere in the comment column, specify a Where clause consisting of WHERE comment LIKE '%30!%%' ESCAPE '!'. If you do not specify ESCAPE and the escape character, SQL Server returns all rows containing the string 30.
The following example shows how to search for the string 50% off when 100 or more copies are purchased in the notes column of the titles table in the pubs database:
Select notes FROM titles Where notes LIKE '50%% off when 100 or more copies are purchased' ESCAPE '%'