Asterisk or Percentage Sign: Which Wildcard Works in Microsoft Jet's LIKE Operator?

Wildcard dilemma in Microsoft Jet database engine: asterisk and percent sign

In the Microsoft Jet database engine, the LIKE operator allows pattern matching using multi-character wildcards. However, choosing asterisk (*) or percent sign (%) as wildcard character has always been a confusing issue.

The behavior of wildcards in the Jet engine depends on the ANSI query mode setting:

• ANSI-89 query mode (legacy mode): Use asterisk (*) as a multi-character wildcard.
• ANSI-92 query mode (SQL Server compatibility mode): Use the percent sign (%) as a multi-character wildcard.

These modes are specific to Jet and are not fully compatible with the ANSI/ISO SQL standard.

Interface specific settings:

• The ADO (OLE DB) interface always uses ANSI-92 query mode.
• DAO interface always uses ANSI-89 query mode.
• The ODBC interface allows query modes to be specified explicitly using the ExtendedAnsiSQL flag.
• Starting from version 2003, the MS Access user interface can use either query mode.

Alternative: ALIKE keyword

To avoid confusion and ensure portability, the ALIKE keyword can be used to force the use of ANSI-92 query mode characters (% and _) regardless of the query mode of the interface. However, this keyword is not officially supported and may not be considered SQL-92 compliant.

The above is the detailed content of Asterisk or Percentage Sign: Which Wildcard Works in Microsoft Jet's LIKE Operator?. For more information, please follow other related articles on the PHP Chinese website!