SQL query statement wildcard problem
When using the SQL statement in Access, a wildcard * is used to query. The statement is as follows:
Select * from normal where bookname Like '* h *'
There is no problem in the test in the SQL view of Access, and everything is working. So write the SQL statement into the C # program, and the result is an error when it comes to the query statement, and it is not allowed. So find the Access help file, find the following help:
Compare string expressions with modes in the SQL expression.
Syntax Expression Like "Pattern"
The LIKE operator syntax includes the following sections:
Some describe the SQL expression used in the WHERE clause. String text compared to Expression.
The description can look for field values that match the specified mode via the LIKE operator. For Pattern, you can specify a complete value (for example, a Like "Smith"), you can also use wildcard to find a value within a range (for example, LIKE "SM *").
In the expression, the LIKE operator can be used to compare the field value and the string. For example, if you enter a LIKE "C *" in the SQL query, the query will return all field values starting with the letter C. In the parameter query, you can prompt the user to type the mode you want to search.
The following example returns the beginning of the letter P and the following is any letter between A to F and three numbers:
Like "p [a-f] ###"
The following table shows how to test different modes via Like.
Match type mode match (Return true) Match (return false) Multiple characters A * a aa, aba, abba abc * ab * AA, AABB, XAB AB, BAC Special Character AA A * A AAA Multiple Character AB * AAA AAA Multiple Character AB * Abcdefg , ABC CAB, AAB single character a? a aaa, a3a, aba abbba single number A # A A0A, A1A, A2A AAA, A10A character range [AZ] F, P, J 2, & range [! az] 9 , &,% B, A non-numeric value [! 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/hp01032532052.aspx
///
Help this help, there is no problem, where is the problem? It is more confused. Later, I asked my colleague: Your SQL statement is wrong, the wildcard should be used, not *. But the help inside is *, and I tried everything in Access, and my colleagues can't say it, so I continued to find the answer to the needs. In another helper file, find the following information:
///
The built-in pattern matching method provides a general tool for a string comparison. The wildcards that can be used for the LIKE operator are shown in the table below, as well as numbers and strings that match them.
Matching items in the character expression in Pattern? or _ (underscore) any single character * or% zero or more characters # any single number (0-9) [charlist] any single character in CharList. [! charlist] No individual characters in Charlist.
One or more characters (CharList) enclosed by middle brackets ([]) can be used to match any single character in the expression, and CharList can contain characters in most ANSI characters, including numbers. You can match these symbols themselves by incorporating specific characters such as left brackets ([), question marks (?), Numbers (#) and asterisk (*). You cannot use the right brackets in a group to match it itself, but you can use it as a single character. In addition to the simple character lists enclosed in square brackets, CharList can divide the upper and lower bounds of the range by using a linker symbol (-). For example, when [A-Z] is used in Pattern, if the corresponding character in the Expression contains any uppercase characters between the A to Z, it can be matched. Multiple ranges can be included in square brackets without having to deliver a range. For example, [A-ZA-Z0-9] can match any alphanumeric characters.
Note that the ANSI SQL wildcard (%) and (_) are only valid in Microsoft? Jet 4.x and Microsoft Ole DB Provider for Jet. If used in Microsoft Access or DAO, they are considered text.
Other important rules for pattern matching are as follows:
The exclamation mark (!) Will indicate that if any characters other than Charlist appear in the expression, it is matched. When it is used outside the square bracket, the exclamation mark matches itself. The linger symbol (-) can be used for the beginning of Charlist (after exclamation mark) or at the end to match it itself. In any other location, the hypoth is identified an ANSI character range. When a character range is specified, the characters must be arranged in ascending order (A-Z or 0-100). [A-Z] is a valid mode, [Z-A] is invalid mode. Ignore the character sequence []; it is considered a zero length character (""). Reference address: http://office.microsoft.com/en-cn/assistance/hp010322842052.aspx
///
At this point, the reason is finally found. Since I use wildcard in Access * everything is normal, I will not succeed. And C # is just supporting% wildcard, and it will be wrong! Is this question not a compatibility problem?
Wildcard:
The wildcard description example% contains any string of zero or more characters. Where title Like '% computer%' will find all the book names that contain the word computer in any of the book name. _ (Underline) any single character. WHERE AU_FNAME LIKE '_ean' will look for all 4 letters ending with EAN (Dean, Sean, etc.). [] Specify the range ([A-F]) or any single character in the collection ([ABCDEF]). WHERE AU_LNAME LIKE '[C-P] Arsen' will look up with ARSEN's last name, for example, caSen, larsen, karsen, etc. [^] Does not belong to any individual characters of the specified range ([A-F]) or a collection ([Abcdef]). WHERE AU_LNAME LIKE 'DE [^ l]%' will look for all the authors of DE and after the letters of L.
Use wildcard as a text
You can match the wildcard mode to the string as a text string, and the method is to place wildcards in parentheses. The table below shows an example of using the LIKE keyword and [] wildcard. 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
Matching the pattern of the Escape clause
A string containing one or more special wildcards can be searched. For example, the discounts table in the Customers database may store discount values containing percent (%). To search for a percent sign as a character rather than a wildcard, you must provide an Escape keyword and an escape. For example, a sample database contains columns named Comment, which contains 30% of text. To search any of any of the positions in the Comment column contain any rows of strings, specify the WHERE clause consisting of where comment like '% 30!'. 'Escape'! '. If you do not specify an Escape and an escape, SQL Server will return all rows of string 30.
The following example shows how to search for strings in the Notes column of the Pubs Database Titles table "50% Off When 100 or more Copies Are Purchased":
SELECT Notes from Titles Where Notes Like '50 %% Off When 100 or More Copies Are PurchaseD 'Escape'% '
