Setting SQL criteria using the WHERE clause
Part two of a four-part series of blogs

If you don't want to see a full set of records from a table in SQL, you can use a WHERE statement to filter out unwanted rows, as shown in this blog.

  1. The WHERE Clause in SQL to filter records using criteria
  2. WHERE Criteria with Text Fields: % and _ Wildcards (this blog)
  3. Combining WHERE Criteria
  4. Using Sound in Criteria

This blog is part of our online SQL tutorial blog.  If you want to learn SQL in a classroom environment, have a look at our SQL training courses.

Posted by Andy Brown on 08 November 2012

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.

WHERE Criteria with Text Fields: % and _ Wildcards

Simple Text Criteria

You can use the relational operators shown in the previous part of this blog against text fields, and they'll work in an intuitive way.  Here are 3 examples:

-- show just purchases of apples


WHERE Fruit = 'Apples'


-- show all purchases which aren't cherries


WHERE Fruit <> 'cherries'


-- show purchases coming after raspberries in alphabet


WHERE Fruit >= 'raspberries'

These 3 queries would show the following output:

The results of the 3 queries

The queries return 1, 9 and 2 rows respectively, being:


Forcing SQL Queries to be Case-Sensitive

The queries shown above clearly aren't case-sensitive (for examples, cherries and Cherries are treated as the same thing). 

Case-sensitivity depends on the collation setting for your SQL Server server, database, table or column (yes, you can set it at each level!).  However, the default collation setting assumes case-insensitivity, and I'm not sure you'd ever want to change this.

You can use a different collation method within an individual query as follows:

-- use case-sensitive search

SELECT * FROM tblFruit

WHERE Fruit COLLATE Latin1_General_CS_AS = 'apples'

This query won't return any records, since the fruit name in row 1 of our table was entered as Apples.  If you're interested in reading more on this subject just Google the phrase t-sql case-sensitive collation,  or similar.

Searching for Parts of Text (% Percentage Symbol Wildcard)

Suppose now that you want to list out all the berries in our modest little table.  You can't use:

SELECT * FROM tblFruit

WHERE Fruit = 'berries'

because it won't return any records.  The solution is to use the LIKE keyword to do what's called pattern-matching:

SELECT * FROM tblFruit

WHERE Fruit like '%berries'

This will return the following 3 fruit:

Fruit ending in berries

The 3 fruit whose names end in berries.


The % symbol is a wildcard which can denote any sequence of characters.  For example:

Criteria What it would show
like '%p%' Apples, Grapes, Pears, Raspberries (they all contain a P).
like 'l%' Lemons, Limes (the only two fruit starting with L).
like '%l%' Apples, Blueberries, Lemons, Limes (they all contain an L).

Access and VBA programmers will be used to this concept, but will be tempted to use an asterisk * instead of a percentage sign %.  Sadly, this won't work!

Using an Underscore to Denote a Single Character

Occasionally it can be useful to check where characters are positioned in a string of text.  Here's an example:

SELECT * FROM tblFruit

WHERE Fruit like '__erries'

This will return Cherries but not any berries, since Cherries is the only fruit which matches the pattern of 2 characters (corresponding to the two underscore characters) followed by the text erries.  Here are a couple of practical examples of the use of an underscore character:

Example criterion Used to show
like 'M_' All the addresses in inner Manchester (M being the UK postcode for this).  Addresses in M1, M2, etc will be included, but addresses in M10, M11, etc won't be.
like '____-12-__' All of the dates in December (the year and day can be any values, but the month must be 12).  This assumes that the date is held as a text string (and in any case there are better ways to achieve the same thing).

Combining Wildcards

Fancy a test?  Cover up the right column, and see if you can work out which of the following criteria would include Apples:

Criterion Would it show Apples?
like '%a%' Yes - a % sign can denote any series of characters, including none.
like 'ApP_ES' Yes - by default SQL queries aren't case-sensitive.
like 'Ap%%' Yes (although the second percentage symbol serves no purpose).
like 'A_l%' No (there are two characters between the A and l of Apples, but we've only allowed for one).

Now I've looked at simple criteria for numbers, dates and (this page) text, it's time to combine them to create more complicated WHERE clauses.

This blog has 0 threads Add post