Using Criteria in SQL Queries
Part three of a six-part series of blogs

When you write queries in SQL it's immensely useful to be able to show records matching criteria that you've set. You can do this using the WHERE clause and this blog teaches you how to use it!

  1. Using Criteria in SQL Queries
  2. Criteria Using Numbers
  3. Criteria Using Text (this blog)
  4. Date Criteria in SQL Server
  5. Using AND, OR and NOT in SQL Server Queries
  6. Using NULL in Criteria

This blog is part of our online SQL tutorial blog - however, Wise Owl also run SQL courses for up to 6 people.

Posted by Andrew Gould on 16 January 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Criteria Using Text

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:

Three king kongs

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:

Using IN

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:

Films beginning with king

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:

King in name

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:

Searching for apostrophe

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:

Range of characters

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:

Character range

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:

Films containing numbers

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.

This blog has 0 threads Add post