-
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 four wild card characters:
| Wildcard Character | Description |
|---|
| % | Any string composed of zero or more characters. |
| _ (underscore) | Any single character. |
| [ ] | Any single character matching the specified set or range. |
| [ ^ ] | Any single character not matching the specified set or range. |
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:
(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:
| Pattern | Description |
|---|
| '[4-8]' | Searches for digits 4, 5, 6, 7, and 8. |
| '[^4-8]' | Searches for digits 0, 1, 2, 3, and 9. |
Language(s):
MS SQL Server
