Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 64

The result is:

This is not what we wanted. We mistakenly retrieved all rows that had a 2 in any position in
the value of SKU. To find the products we want, we cannot use the SQL wildcard character
%. Instead, we must use the SQL underscore (_) wildcard character , which represents a
single, unspecified character in a specific position. The following SQL statement will find all
SKU_DATA rows with a value of 2 in the third position from the right:

/* *** SQL-Query-CH02-24 *** */
SELECT *
FROM SKU_DATA

WHERE SKU LIKE '%2__';

Observe that there are two underscores in this SQL query—one for the first position on the right
and another for the second position on the right. This query gives us the result that we want:

Does Not Work with
Microsoft access
aNSI-89 SQL

Microsoft Access ANSI-89 SQL uses wild-
cards, but not the SQL-92 standard wildcards.
Microsoft Access uses the Microsoft access
question mark (?) wildcard character instead
of an underscore (_) to represent a single
character.

Solution: Use the Microsoft Access question mark (?) wildcard in place of the SQL-92
underscore (_) wildcard in Microsoft Access ANSI-89 SQL statements. Thus, the pre-
ceding SQL query would be written as follows for Microsoft Access:

/* *** SQL-Query-CH02-24-Access *** */
SELECT *
FROM SKU_DATA

WHERE SKU LIKE '*2??';

Furthermore, Microsoft Access can sometimes be fussy about stored trailing spaces in
a text field. You may have problems with a WHERE clause like this:

WHERE SKU LIKE '10?200';

Solution: Use a trailing asterisk (*), which allows for the trailing spaces:

WHERE SKU LIKE '10?200*';