DevGuru Jet SQL LIKE Operation

Quick References

Features

Partners

OPERATION: LIKE

SELECT list
FROM table
WHERE expression [NOT] LIKE 'string'

The LIKE operator allows you to compare values in a field to a string (or pattern) and see if there is a match. In the first example, a list is created of authors who have written a 'Cook Book'.

SELECT AuthorName FROM BookList
WHERE BookType LIKE 'Cook Book'

By using NOT, you can select everything that does not match a specific string. Adding NOT to the previous example will create a list of all authors in BookList who have not written a 'Cook Book'.

SELECT AuthorName FROM BookList
WHERE BookType NOT LIKE 'Cook Book'

The LIKE operator can use what are called "wild card characters" to create search pattern strings. Here are five wild card characters:

Wild Card	Description
*	Any string composed of zero or more characters
?	Any single character
#	Any single digit
[ ]	Any single character matching the specified set or range
[ ! ]	Any single character not matching the specified set or range

The use of wild card characters, in the form of a regular expression, can be a very complex subject. Fortunately, there are several simple ways to use wild cards that are very useful for pattern matching. By placing a * before and after the string 'Cook Book' (i.e., '*Cook Book*' ) you can search for a book title that contains the string 'Cook Book'. The * will accept zero or more characters in the book title before or after the phrase 'Cook Book'.

SELECT AuthorLastName FROM BookList
WHERE BookTitle LIKE '*Cook Book*'

Wild card characters can be combined together to create search patterns. In the next example, the expression '[PT]*' searches for two patterns. It will look for all strings that start with a P and have zero or more additional characters after the P. And it will also look for all strings that start with a T and have zero or more additional characters after the T. So, Thompson would be selected, but not Cussler.

SELECT AuthorLastName FROM BookList
WHERE AuthorLastName LIKE '[PT]*'

In the previous example, by adding a dash (-) between the P and the T, the search pattern will look for all authors whose last name starts with P, Q, R, S, and T.

SELECT AuthorLastName FROM BookList
WHERE AuthorLastName LIKE '[P-T]*'

Here are a few simple examples of possible search patterns for numbers:

'[4-8]'	Search for digits 4, 5, 6, 7, and 8
'[!4-8]'	Search for digits 0, 1, 2, 3, and 9
'Chapter #'	Search for digits 0-9 after the word Chapter

Note:
The * wildcard should only be used through Access SQL View interface or through Data Access Objects. The % wildcard should be used in its place if you are using Jet OLE DB or ADO.