sql - Microsoft Jet wildcards: asterisk or percentage sign?

Question

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.

Answer 1

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).