SQL advanced -> Using wildcards in MS-Access LIKE function

The use of wildcards inside LIKE function can save a lot of effort when you write query that looks for some pattern in character string. The wildcards are:
* substitute for unlimited amount of characters at the BEGINING/END of the string.
? substitute for one character inside square brakets
! substitute for number of character inside square brakets

Look for Customer names that begins with "A" and fifth char is "n":
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName LIKE 'A???n*';

The result will be like this:
CustomerIDCustomerName
14Antonella Musentah
23Alain Fishborn
26Arian Aspin

Look for Customer names that have characters "x", "y", "z":
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName LIKE '*[x-z]*';

The result will be like this:
CustomerIDCustomerName
13Aida Yespica
16Valentina Mazepa
20Patrick Swayze
21Marlon Zeana
25Xena Aspin

Look for Customer names that starts with "M" and have characters "e" or "a" somewhere inside:
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName LIKE 'M[!ea]*';

The result will be like this:
CustomerIDCustomerName
19Michael Ballack

But as for my expirience expressions LIKE '*[!abc]*' not allways work properly in MS-Access queries. Sometimes they return very unexpected results.


sqlexamples.info