Microsoft Jet wildcards: asterisk or percentage sign?

by

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

More Related Contents:

  1. sql sum data from multiple tables
  2. Case statement for multiple columns
  3. Transaction lock in sql
  4. SQL where clause odd behavior [closed]
  5. SQL Server Exec issue [closed]
  6. Count(*) vs Count(1) – SQL Server
  7. Selecting data from two different servers in SQL Server
  8. PostgreSQL: running count of rows for a query ‘by minute’
  9. Is a view faster than a simple query?
  10. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  11. SQL SELECT WHERE field contains words
  12. How to use the ‘as’ keyword to alias a table in Oracle?
  13. FULL OUTER JOIN with SQLite
  14. How to include “zero” / “0” results in COUNT aggregate?
  15. SQL SELECT speed int vs varchar
  16. mySQL :: insert into table, data from another table?
  17. Return 0 if field is null in MySQL
  18. Define variable to use with IN operator (T-SQL)
  19. Sql script to find invalid email addresses
  20. ORA 00904 Error:Invalid Identifier
  21. postgresql: INSERT INTO … (SELECT * …)
  22. Does Sql JOIN order affect performance?
  23. Using tuples in SQL “IN” clause
  24. Unfindable SQLite Syntax Error
  25. Inserting into Oracle and retrieving the generated sequence ID
  26. Converting String List into Int List in SQL
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. Convert bigint to datetime
  29. Replacing NULL and empty string within Select statement
  30. Why isn’t row level security enabled for Postgres views?

Leave a Comment