sql - Why does a LIKE query in Access not return any records?

Question

Is there any reason why

SELECT * FROM MyTable WHERE [_Items] LIKE '*SPI*'

does not return any records with OleDbAdapter.Fill(DataSet) or OleDbCommand.ExecuteReader()?

When I run the same SQL in MS Access directly, it returns the expected records. Also, in the same code, if I change the SQL to

 SELECT * FROM MyTable 

all records are returned.

Answer 1

Try changing LIKE to ALIKE and your wildcard characters from * to %.

The Access Database Engine (Jet, ACE, whatever) has two ANSI Query Modes which each use different wildcard characters for LIKE:

• ANSI-89 Query Mode uses *

• ANSI-92 Query Mode uses %

OLE DB always uses ANSI-92 Query Mode. DAO always uses ANSI-89 Query Mode. The Access UI can be set to use one or the other.

However, when using ALIKE keyword the wildcard character is always % regardless of ANSI Query Mode.

Consider a business rule that states a data element must consist of exactly eight numeric characters. Say I implemented the rule as follows:

CREATE TABLE MyStuff 
(
 ID CHAR(8) NOT NULL, 
 CHECK (ID NOT LIKE '%[!0-9]%')
);

It is inevitable that I would use % as the wildcard character because Access's CHAR data type and CHECK constraints can only be created in ANSI-92 Query Mode.

However, someone could access the database using DAO, which always uses ANS-89 Query Mode, and the % character would be considered a literal rather than a 'special' character, and the following code could be executed:

INSERT INTO MyStuff (ID) VALUES ('%[!0-9]%');

the insert would succeed and my data integrity would be shot :(

The same could be said by using LIKE and * in a Validation Rule created in ANSI-89 Query Mode and someone who connects using ADO, which always uses ANSI-92 Query Mode, and INSERTs a * character where a * character ought not to be.

As far as I know, there is no way of mandating which ANSI Query Mode is used to access one's Access database. Therefore, I think that all SQL should be coded to behave consistently regardless of ANSI Query Mode chosen by the user.

Note it is not too difficult to code for both using LIKE with the above example e.g.

CHECK (
       ID NOT LIKE '%[!0-9]%'
       AND ID NOT LIKE '*[!0-9]*'
      )

...or indeed avoid wildcards completely e.g.

CHECK (ID LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

However, using ALIKE will result in less verbose code i.e. easier for the human reader and therefore easier to maintain.

Also, when the time comes to port to a SQL product that is compliant with SQL Standards, ALIKE ports well too i.e. transforming the ALIKE keyword to LIKE is all that is required. When parsing a given SQL predicate, it is far, far easier to locate the one LIKE keyword in than it is to find all the multiple instances of the * character in text literals. Remember that "portable" does not mean "code will run 'as is'"; rather, it is a measure of how easy it is to move code between platforms (and bear in mind that moving between versions of the same product is a port e.g. Jet 4.0 to ACE is a port because user level security no longer functions, DECIMAL values sort differently, etc).