What Wildcard Character Should I Use with the Jet LIKE Operator, and Why?

Microsoft Jet LIKE Operator: Detailed explanation of wildcards

In Microsoft Jet databases, the LIKE operator is used to pattern match field values. However, selecting wildcard characters for multi-character matching has always been a confusing issue.

Question: What is the correct multi-character wildcard in Jet's LIKE operator? What factors will influence its choice?

Answer:

The behavior of wildcards in Jet depends on the ANSI query mode of the interface used:

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

Interface specific mode:

• ADO (OLE DB) always runs in ANSI-92 query mode.
• DAO always runs in ANSI-89 query mode.
• ODBC query mode can be set explicitly using the ExtendedAnsiSQL flag.
• MS Access user interface supports two modes.

ALIKE Keyword:

Jet SQL syntax also provides the ALIKE keyword, allowing you to use ANSI-92 characters (% and _) regardless of the interface's query mode. However, ALIKE is not an official SQL standard and may not be supported in all environments.

The above is the detailed content of What Wildcard Character Should I Use with the Jet LIKE Operator, and Why?. For more information, please follow other related articles on the PHP Chinese website!