This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. The WHERE Clause in SQL to filter records using criteria WHERE Criteria with Text Fields: % and _ Wildcards (this blog) Combining WHERE Criteria 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.

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.

The WHERE Clause in SQL to filter records using criteria
WHERE Criteria with Text Fields: % and _ Wildcards (this blog)
Combining WHERE Criteria
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

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.

Parts of this blog
The WHERE Clause in SQL to filter records using criteria WHERE Criteria with Text Fields: % and _ Wildcards (this blog) Combining WHERE Criteria Using Sound in Criteria

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

WHERE Criteria with Text Fields: % and _ Wildcards

Simple Text Criteria

Forcing SQL Queries to be Case-Sensitive

Searching for Parts of Text (% Percentage Symbol Wildcard)

Using an Underscore to Denote a Single Character

Combining Wildcards

Head office

London

Manchester