Using criteria to search for text can be a little more tricky than searching for numbers. This page explains a variety of techniques for working with text criteria.
Finding Exact Matches
Searching for an exact string of text in a field is very similar to searching for an exact number: you simply ask if the field is equal to the string of text you're looking for. The query shown below will display all of the films whose name is exactly equal to king kong.
SELECT
FilmName
,FilmReleaseDate
FROM
tblFilm
WHERE
FilmName = 'king kong'
Notice that you have to enclose the string of text you're searching for in a set of single quotes. The results of this query are shown below:
There are three films with the name King Kong in our database.
You might notice from the above query that, by default, searching for text is not case sensitive in SQL Server.
Using IN with Text Criteria
You can use the IN operator to search for a list of separate strings, as in the example below:
SELECT
FilmName
,FilmReleaseDate
FROM
tblFilm
WHERE
FilmName IN ('die hard','lethal weapon','total recall')
The above query searches for three different film titles. Each title is separated from the next using a comma. The results of the query are shown below:
The IN operator only finds exact matches.
Using Wildcards to Find Similar Text
If you don't want to find an exact match for a string of text you can use the LIKE operator instead of =. If you do this you'll also need to use one of the two wildcard characters, as described in the table below:
Wildcard | What it means |
|---|
% | The percentage symbol represents any quantity of any characters (text, numbers or punctuation), including no characters at all. |
_ | The underscore represents any one character of any type. |
The query below will return any films whose name begins with the word king and is followed by anything else:
SELECT
FilmName
,FilmReleaseDate
FROM
tblFilm
WHERE
FilmName LIKE 'king%'
The LIKE operator replaces the = sign in criteria which use wildcards. The wildcard character must be included within single quotes. The results for the query are shown below:
Four films in this database begin with the word king.
You can use multiple wildcards in a single search string. The example below looks for any film which contains the word king:
SELECT
FilmName
,FilmReleaseDate
FROM
tblFilm
WHERE
FilmName LIKE '%king%'
Using a % sign at the start and end of the string will find the word king anywhere within the film's name. The results are shown below:
This would also work for films with the word king in the middle of the name.
Finding Special Characters in a String
What happens if one of the special SQL characters, such as a wildcard, is part of the string of text you're looking for? The answer, in most cases, is to enclose the character you're searching for in a set of square brackets. In the example below we're trying to find any film whose description contains a % symbol:
SELECT
FilmName
,FilmSynopsis
FROM
tblFilm
WHERE
FilmSynopsis LIKE '%[%]%'
In the above query the first and third % symbols are treated as wildcards, while the middle % symbol is treated as a character in a string. As it happens, none of our films have a % symbol in their description, so we wouldn't return any results. If we didn't use the square brackets to contain the % symbol, the query would return all of the films from the table.
Unfortunately, the square brackets trick doesn't work if you're looking for a single quote character. In the example below we're attempting to look for an apostrophe in the film's description:
SELECT
FilmName
,FilmSynopsis
FROM
tblFilm
WHERE
FilmSynopsis LIKE '%[']%'
The problem is that the apostrophe that we place inside the square brackets is treated as the closing character for a string of text - the result is a syntax error. To search for an apostrophe as a character in a string you can simply type in two apostrophes together:
SELECT
FilmName
,FilmSynopsis
FROM
tblFilm
WHERE
FilmSynopsis LIKE '%''%'
The results of this query are shown below:
The results all include an apostrophe in the FilmSynopsis column.
Specifying a Range of Characters
What if you wanted to find a list of films whose name begins with a, b or c? You can put a range of characters inside a set of square brackets, as shown in the example below:
SELECT
FilmName
,FilmReleaseDate
FROM
tblFilm
WHERE
FilmName LIKE '[abc]%'
The query above looks for films whose name begins with a, b or c followed by any other characters. The results are shown below:
The resulting films all begin with a, b or c.
The letters you put in square brackets don't have to be in a particular sequence. Our criteria could be FilmName LIKE '[wise]%' for example. The results would be a list of films whose names begin with any of those four letters.
When you're searching for a range of characters in a sequence you don't have to type in each letter that you're searching for. The example below finds any film whose name begins with x, y or z:
SELECT
FilmName
,FilmReleaseDate
FROM
tblFilm
WHERE
FilmName LIKE '[x-z]%'
The results of this query are shown below:
As it turns out the database doesn't contain any films whose name begins with the letter z.
This technique is also useful for finding numbers within a string. The query below will find any films which have a number in their name:
SELECT
FilmName
,FilmReleaseDate
FROM
tblFilm
WHERE
FilmName LIKE '%[0-9]%'
The results of the query are shown below:
Any film with a number in its name will appear in the results.
What's Next?
The final type of data you'll need to learn about is date and time information. The next part of this blog series teaches you about using dates in the WHERE clause of a query.
