Menu
What is the proper multi-character wildcard in the LIKE operator in Microsoft Jet and what setting affects it (if any)? I am supporting an old ASP application which runs on Microsoft Jet (on an Access database) and it uses the % symbol in the LIKE operator, but I have a customer who apparently has problems in his environment because the % character is understood as a regular character, and I assume that his multi-character wildcard is *. Also, I'm almost sure that in the past I have written application with queries using * instead of %. Finally, Microsoft Access (as an application) also works only with * and not % (but I'm not sure how relevant it is).
I just spent about 20 minutes searching the Internet without any useful results, and so I thought it would be useful ask on stackoverflow. Somebody may already know it, and it's better to keep the potential answers on stackoverflow than any other random discussion forum anyway.
361
Match all characters anywhere in your data In the Criteria cell under the field you want to use, add an asterisk on either side of your criteria, or on both sides. For example: "*owner*".
The asterisk “*” and the question mark “?” are the two main wildcard characters in Access you need to know. The asterisk represents multiple unknown characters. For example, the criteria “N*” would find all “N” words like “Nebraska,” “Ned,” “Not,” “Never Ever,” etc. The question mark represents one unknown character.
The most common wildcards are the asterisk (*), which represents one or more characters and question mark (?) that represents a single character.
The straight answer is that the behaviour of the wildcard characters is dependent on the ANSI Query Mode of the interface being used.
ANSI-89 Query Mode ('traditional mode') uses the * character, ANSI-92 Query Mode ('SQL Server compatibility mode') uses the % character. These modes are specific to ACE/Jet and bear only a passing resemblance to the ANSI/ISO SQL-89 and SQL-92 Standards.
The ADO interface (OLE DB) always uses ANSI-92 Query Mode.
The DAO interface always uses ANSI-89 Query Mode.
When using ODBC the query mode can be explicitly specified via the ExtendedAnsiSQL flag.
The MS Access user interface, from the 2003 version onwards, can use either query mode, so don't assume it is one or the other at any given time (e.g. do not use query-mode-specific wildcard characters in Validation Rules).
ACE/Jet SQL syntax has an ALIKE keyword, which allows the ANSI-92 Query Mode characters (% and _) regardless of the query mode of the interface, however has the slight disadvantage of the ALIKE keyword not being SQL-92 compatible (however ALIKE remains highly portable). The main disadvantage, however, is that I understand the ALIKE keyword is not officially supported (though I can't imagine it will disappear or have altered behaviour anytime soon).
195
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
