Microsoft Access SQL Keywords: LIKE

	SQL Keywords: LIKE

Description

The LIKE keyword is used to create a pattern for a condition. The formula to follow is:

Expression LIKE pattern

The Expression factor is the expression that will be evaluated. This must be a clear and valid expression.

The pattern factor can be a value to be found in Expression. For example, it can be the same type of value used in a WHERE statement. In this case, the equal operator would be the same as LIKE. For example

SELECT Employees.DateHired, Employees.FirstName,
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName)="Scott"));

is equivalent to

SELECT Employees.DateHired, Employees.FirstName, 
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) Like "Scott"));

The idea of using a LIKE operator is to give an approximation of the type of result you want. The available wildcards to se with the LIKE operator are:

LIKE Any Character *

To match any character, in any combination, for any length, use the * wildcard. You can precede it with a letter, as in S*. Here is an example

SELECT Employees.DateHired,
       Employees.FirstName,
       Employees.LastName,
       Employees.Department
FROM Employees
WHERE Employees.LastName LIKE 'S*';

To negate the condition, you can precede the criterion with NOT after WHERE. Here is an example:

SELECT Employees.DateHired,
       Employees.FirstName,
       Employees.LastName,
       Employees.Department
FROM Employees
WHERE NOT (Employees.LastName LIKE 'S*');

You can type the NOT operator before the LIKE expression. Here is an example:

SELECT Employees.DateHired,
       Employees.FirstName,
       Employees.LastName,
       Employees.Department
FROM Employees
WHERE (Employees.LastName) NOT LIKE 'S*';

When you precede the * character with a letter, only that letter would be considered. Alternatively, you can specify a group of characters that would precede the * symbol. Here is an example:

SELECT Employees.DateHired,
       Employees.FirstName,
       Employees.LastName,
       Employees.Department
FROM Employees
WHERE Employees.LastName LIKE 'San*';

LIKE a Range of Characters []

To consider a range of characters from the alphabet, include the range in square brackets: LIKE "[E-H]*". Here is an example:

SELECT Employees.DateHired,
       Employees.FirstName,
       Employees.LastName,
       Employees.Department
FROM Employees
WHERE Employees.LastName Like '[E-H]*';

Once again, remember that you can negate this expression by preceding it with NOT.

LIKE an Unknown Character ?

If you know a certain pattern in the string but you are forgetting only one letter, you can use the ? to precede it. Here is an example:

SELECT 	Employees.DateHired, Employees.FirstName,
	Employees.LastName, Employees.Department
FROM Employees
WHERE Employees.FirstName Like "?aul");

A Parameter Query

You can use the LIKE keyword with the * wildcard to create a parameterized query. Here is an example:

LIKE "*" & [A director name that includes] & "*"