WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
Wise Owl Training
See 525 reviews for our classroom and online training
If you found this blog useful and youâ€™d like to say thanks you can click here to make a contribution. Thanks for looking at our blogs!

BLOGS BY TOPIC

BLOGS BY AUTHOR

BLOGS BY YEAR

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

SELECT * FROM tblFRUIT

WHERE Fruit = 'Apples'

-- show all purchases which aren't cherries

SELECT * FROM tblFRUIT

WHERE Fruit <> 'cherries'

-- show purchases coming after raspberries in alphabet

SELECT * FROM tblFRUIT

WHERE Fruit >= 'raspberries'

These 3 queries would show the following output:

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:

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.