Microsoft Jet Wildcard Confusion: * or %? Which Wildcard Should I Use with LIKE?

Microsoft Jet Wildcard: Solve the mystery of * and %

In Microsoft Jet, the correct multi-character wildcard for the LIKE operator depends on the ANSI query mode setting.

ANSI Query Mode

Microsoft Jet supports two query modes:

• ANSI query mode ("legacy mode") : Use the "*" wildcard character.
• ANSI query mode ("SQL Server compatibility mode") : Use the "%" wildcard character.

Interface specific query mode

The query mode used depends on the interface used:

• ADO interface (OLE DB) : Always use ANSI-92 query mode (%).
• DAO interface: Always use ANSI-89 query mode (*).
• ODBC interface: Query mode can be specified explicitly via the ExtendedAnsiSQL flag.
• MS Access User Interface: Starting from version 2003, any query mode can be used.

ALIKE keyword

To avoid confusion, you can use the ALIKE keyword in Jet SQL syntax. Regardless of the query mode, ALIKE allows the ANSI-92 wildcard character (%). However, it is not compatible with SQL-92 and, although widely used, may not be officially supported.

Therefore, the appropriate wildcard for the LIKE operator in Microsoft Jet depends on the query mode used, which is determined by the interface used. To ensure consistency, it is recommended to use the * wildcard for traditional query mode and the % wildcard for SQL Server compatibility mode. If desired, the ALIKE keyword can also be used to ensure compatibility across different query modes.

The above is the detailed content of Microsoft Jet Wildcard Confusion: * or %? Which Wildcard Should I Use with LIKE?. For more information, please follow other related articles on the PHP Chinese website!