JetSQL » Operators » LIKE
- Syntax:
-
SELECT fieldlist
FROM table
WHERE expression [ NOT ] LIKE 'string' - fieldlist
- The list of fields that are to be retrieved from the table.
- table
- The name of the table from which the information is being retrieved.
- expression
- The field on which the string or pattern is to be tested.
- string
- The string or pattern to search for in the expression. It can include valid wildcard characters.
The LIKE operator allows you to compare values in a field to a string (or pattern) and see if there is a match.
Examples
Code:
SELECT AuthorLastName AS LastName FROM BookList
WHERE BookType LIKE 'Cook Book';
Output:
LastName Stewart Craig Benson
(3 row(s) affected)
Explanation:
In this example, a list is created of authors who have written a 'Cook Book'.
Language(s): MS SQL Server
Code:
SELECT AuthorLastName AS LastName FROM BookList
WHERE BookType NOT LIKE 'Cook Book';
Output:
LastName King Twain Barclay Crane London O'Neill Pinder
(7 row(s) affected)
Explanation:
By using NOT, you can select everything that does not match a specific string. The addition of NOT in this example creates a list of all authors in the 'BookList' table who have not written a 'Cook Book'.
The LIKE operator can use what are called "wildcard characters" to create search pattern strings. Here are five wild card characters:
| Wildcard Character | 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. |
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.
Language(s): MS SQL Server
Code:
SELECT AuthorLastName AS LastName, BookTitle FROM BookList
WHERE BookTitle LIKE '*Cook Book*';
Output:
LastName BookTitle Stewart Martha Stewart's Cook Book Craig Losing Weight Cook Book Benson Southern Cook Book and Health Guide
(3 row(s) affected)
Explanation:
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'.
Language(s): MS SQL Server
Code:
SELECT AuthorLastName AS LastName FROM BookList
WHERE AuthorLastName LIKE '[PT]*';
Output:
LastName Twain Pinder
(2 row(s) affected)
Explanation:
Wild card characters can be combined together to create search patterns. In this 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, Twain would be selected, but not Cussler.
Language(s): MS SQL Server
Code:
SELECT AuthorLastName AS LastName FROM BookList
WHERE AuthorLastName LIKE '[P-T]*';
Output:
LastName Stewart Twain Pinder
(3 row(s) affected)
Explanation:
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.
Here are a few simple examples of possible search patterns for numbers:
| '[4-8]' | Searches for digits 4, 5, 6, 7, and 8. |
| '[!4-8]' | Searches for digits 0, 1, 2, 3, and 9. |
| 'Chapter #' | Searches for digits 0-9 after the word Chapter |
Language(s): MS SQL Server
