Microsoft Jet Wildcards: Asterisk or Percentage Sign

Microsoft Jet wildcards: asterisk or percentage sign?

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

ADODB wild card operators other than %

Match any single alphabetic character:

?

is equivalent to

[A-Za-z]

---

Match any single numeric character:

#

is equivalent to

[0-9]

Wildcards in MS Access SQL

In Access query builder's dialect of sql, you need ? not _. If you connect to the same mdb backend via odbc you'll need to go back to using the standard wildcards.

See this page for details.

How to use literal underscore in Access SQL wildcard query via OLEDB in c#?

Put the underscore inside a "character range", delimited by square brackets. Access will then understand you want it treated as the literal underscore character instead of as the ANSI 92 mode (which you're using with OleDb) wildcard which matches any single character.

WHERE OptionID Like '%[_]%'

---

Related Topics