This page has 0 threads | Add post

WHERE Criteria with Text Fields: % and _ Wildcards

Written by Andy Brown

In this tutorial

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

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.

Previous
tutorial

WHERE criteria (version A)

Previous
part

The WHERE Clause in SQL to filter records using criteria

Top of page

Next
part

Combining WHERE Criteria

Next
tutorial

Calculations and Expressions

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

WHERE criteria (version B)

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