How to Use Wildcards in SQL

A wildcard character is used to substitute one or more characters in a string. Typically, you can apply SQL wildcard characters with the LIKE operator in the WHERE clause to find a specified pattern in a column and replace it in case of need.

Wildcard characters in SQL Server

Character Description Example
% Represents zero or more characters br% finds br, bracelet, break, and brink
_ Represents a single character f_r finds far, fir, for, and fur
[] Represents any single character within the brackets s[ia]nk finds sink and sank, but not sunk
[^] Represents any character but not those specified in the brackets s[^ia]nk finds sunk, but not sink and sank
[-] Represents any single character within the specified range s[e-o]n finds sin and son, but not sun
Note
Wildcard characters vary depending on the database management system you are using. For instance, in Microsoft Access, * represents zero or more characters, ? represents a single character, and # narrows the search down to a single numeric character.

Another thing worth mentioning here is that all wildcards can be used in combinations to make your search more flexible.

Demo database

Let us illustrate the wildcard syntax on the AdventureWorks2019 sample database. Here are the first 15 records from the Person.Address table that we will use in our examples.

AddressID AddressLine1 AddressLine2 City StateProvinceID PostalCode
1 714 Pine Tree St 4th FL Riverside 7 64467
2 1026 New Rock Hill Circle 99th FL Wilkers-Barre 29 79892
3 1759 W Fox Hill Ave Standard Bldg Algonquin 24 29820
4 2433 West Ashwood Way 99th FL Wilkers-Barre 29 79892
5 282 West Burwood Cir 79th Floor Anna 50 39435
6 1263 Riverside Way 79th Floor Anna 50 39435
7 61 Mount Court 71th Floor Gallup 10 49517
8 61 Mount Court 46th Floor Ottawa 8 63180
9 105 Meadowview Road 8th Floor Salem 11 23395
10 1416 Burwood Highway 79th Floor Anna 50 39435
11 3317 White Stonewood Loop 65th FL Kaufman 25 17678
12 1274 NE Oak Pkwy 65th FL Kaufman 25 17678
13 574 Woodrow Way 49th FL Altus 37 69585
14 1026 New Rock Hill Circle 74th FL Seaside 26 67078
15 2433 West Ashwood Way 74th FL Seaside 26 67078

Examples

With the demo database at hand, here are several examples that will show you wildcards in action.

Example 1
Let's start with the % wildcard and execute the following query against our demo table
SELECT * FROM Person.Address WHERE City LIKE 'S%';
With all records that feature cities starting with S firmly in place:
AddressID AddressLine1 AddressLine2 City StateProvinceID PostalCode
9 105 Meadowview Road 8th Floor Salem 11 23395
14 1026 New Rock Hill Circle 74th FL Seaside 26 67078
15 2433 West Ashwood Way 74th FL Seaside 26 67078
Example 2
We'll use the _ wildcard and search for all records with AddressID starting with 1
SELECT * FROM Person.Address WHERE AddressID LIKE '1_';
The output will be as follows, with both 10 and 50 being valid values:
AddressID AddressLine1 AddressLine2 City StateProvinceID PostalCode
10 1416 Burwood Highway 79th Floor Anna 50 39435
11 3317 White Stonewood Loop 65th Floor Kaufman 25 17678
12 1274 NE Oak Pkwy 65th Floor Kaufman 25 17678
13 574 Woodrow Way 49th Floor Altus 37 69585
14 1026 New Rock Hill Circle 74th Floor Seaside 26 67078
15 2433 West Ashwood Way 74th Floor Seaside 26 67078
Example 3
And what if we want to select all cities starting with G, R, and S? Let's see
SELECT * FROM Person.Address WHERE City LIKE '[grs]%';
Note how we have combined two wildcards in a single query. The output will give us just what we need:
AddressID AddressLine1 AddressLine2 City StateProvinceID PostalCode
1 714 Pine Tree St 4th FL Riverside 7 64467
7 61 Mount Court 71th Floor Gallup 10 49517
9 105 Meadowview Road 8th Floor Salem 11 23395
14 1026 New Rock Hill Circle 74th FL Seaside 26 67078
15 2433 West Ashwood Way 74th FL Seaside 26 67078
Example 4
As opposed to the previous case, let's exclude all records with cities starting with A, S, and W from our output
SELECT * FROM Person.Address WHERE City LIKE '[^asw]%';
After we execute the query, this is what we will get:
AddressID AddressLine1 AddressLine2 City StateProvinceID PostalCode
1 714 Pine Tree St 4th FL Riverside 7 64467
7 61 Mount Court 71th Floor Gallup 10 49517
8 61 Mount Court 46th Floor Ottawa 8 63180
11 3317 White Stonewood Loop 65th FL Kaufman 25 17678
15 2433 West Ashwood Way 74th FL Seaside 26 67078
Example 5
Finally, let's show you how to specify a range of characters. Say, we want to retrieve all the cities in our table starting with any letter between (and including) G and O
SELECT * FROM Person.Address WHERE City LIKE '[g-o]%';
The output will be as follows:
AddressID AddressLine1 AddressLine2 City StateProvinceID PostalCode
7 61 Mount Court 71th Floor Gallup 10 49517
8 61 Mount Court 46th Floor Ottawa 8 63180
11 3317 White Stonewood Loop 65th FL Kaufman 25 17678
12 1274 NE Oak Pkwy 65th FL Kaufman 25 17678

Use wildcards in dbForge SQL Complete

You can apply wildcards during your daily work with SQL queries in dbForge SQL Complete, an add-in for SSMS and Visual Studio that focuses on code completion, formatting, and refactoring. Additionally, you can use wildcard expansion to quickly insert a list of columns, available in your tables and views, into your SELECT statement.

dbForge SQL Complete

Advanced solution for SQL database development, management, and administration

Try now
Free edition available
Try dbForge SQL Complete and double the speed of your SQL coding! Try now

We use cookies to provide you with a better experience on the Devart website. You can read more about our use of cookies in our Cookies Policy.
Click OK to continue browsing the Devart site. Be aware you can disable cookies at any time.