dbForge SQL Complete

An advanced IntelliSense-style code completion add-in for SSMS and VS

How to Use SQL Wildcard Characters

What is a wildcard in SQL and how to perform a SQL wildcard search? On this page, you can find answers and explore practical examples of wildcard usage. Whether you're a beginner or an experienced database user, understanding how to use wildcards effectively can greatly enhance your data querying skills.

What is a wildcard in SQL?

A wildcard in SQL is a special symbol 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 search for a specific pattern within a column. They enable you to retrieve data even when the exact value is unknown, making it easier to perform flexible, pattern-based searches and narrow down results efficiently.

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

SQL wildcard 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	City	StateProvinceID	PostalCode
9	105 Meadowview Road	Salem	11	23395
14	1026 New Rock Hill Circle	Seaside	26	67078
15	2433 West Ashwood Way	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	City	StateProvinceID	PostalCode
10	1416 Burwood Highway	Anna	50	39435
11	3317 White Stonewood Loop	Kaufman	25	17678
12	1274 NE Oak Pkwy	Kaufman	25	17678
13	574 Woodrow Way	Altus	37	69585
14	1026 New Rock Hill Circle	Seaside	26	67078
15	2433 West Ashwood Way	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	City	StateProvinceID	PostalCode
1	714 Pine Tree St	Riverside	7	64467
7	61 Mount Court	Gallup	10	49517
9	105 Meadowview Road	Salem	11	23395
14	1026 New Rock Hill Circle	Seaside	26	67078
15	2433 West Ashwood Way	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	City	StateProvinceID	PostalCode
1	714 Pine Tree St	Riverside	7	64467
7	61 Mount Court	Gallup	10	49517
8	61 Mount Court	Ottawa	8	63180
11	3317 White Stonewood Loop	Kaufman	25	17678
15	2433 West Ashwood Way	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	City	StateProvinceID	PostalCode
7	61 Mount Court	Gallup	10	49517
8	61 Mount Court	Ottawa	8	63180
11	3317 White Stonewood Loop	Kaufman	25	17678
12	1274 NE Oak Pkwy	Kaufman	25	17678

Performance considerations when using SQL wildcards

When using SQL wildcards, especially with large datasets, optimizing your queries to avoid performance bottlenecks is important. Here are some key guidelines to help you achieve efficient wildcard searches:

Optimize wildcard usage: Using a wildcard operator in SQL at the beginning of a pattern (for example, '%pattern') forces the database to perform a full table scan, which can significantly slow down query performance. To improve efficiency, structure your queries to use trailing wildcards (for example, 'pattern%') so that the query engine can take advantage of indexes. Additionally, avoid using multiple leading wildcards or combining them with underscores (for example, '%_pattern'), as these patterns prevent index usage and increase the processing load.
Reduce wildcard chaining: Excessive wildcard chaining in SQL queries can lead to poor performance and return overly broad or irrelevant results. For example, instead of using multiple wildcards like SELECT * FROM Products WHERE Description LIKE '%computer%%soft%';, use more focused conditions such as SELECT * FROM Products WHERE Description LIKE '%computer%' AND Description LIKE '%soft%'; to improve both performance and result relevance.
Use indexes wisely: In SQL Server, wildcard patterns that begin with a known prefix (such as 'pattern%') allow the query engine to use indexes efficiently. However, patterns that begin with a wildcard (forexample, '%pattern') or include wildcards in the middle (such as 'pro%ct', which matches "product" or "project"), prevent index usage and often lead to full table scans. To optimize performance, index those columns that are frequently searched using prefix-based wildcards.
Limit the scope of your searches: Whenever possible, narrow the result set by adding additional conditions in your WHERE clause (for example, date ranges or category filters). This reduces the number of rows that need to be scanned and improves performance even when wildcards are used.
Monitor wildcard query performance: To avoid query timeouts and high resource usage, test and profile wildcard queries. In SQL Server, you can view the execution plan to see how a query is processed. The execution plan usually shows estimated and actual row counts, table scans, index usage, and join strategies.

Common mistakes when using SQL wildcards

When working with SQL Server wildcards, developers sometimes make mistakes that can affect query accuracy or performance. Here are some of the most common pitfalls:

Placing % at the beginning of the pattern: Using a leading wildcard character in SQL prevents the use of indexes, resulting in full table scans and slow performance—especially on large datasets. Whenever possible, use a trailing wildcard (for example, 'pattern%') to allow index optimization.
Overusing wildcards: Using wildcards when an exact match is known adds unnecessary overhead and can lead to slower queries. Use = for exact comparisons instead. For example, instead of LIKE '%pattern%', use = 'pattern'.
Misunderstanding the underscore: The underscore (_) wildcard represents exactly one character. A common mistake is expecting it to match multiple characters. For example, LIKE 'J__' will only match strings with exactly three characters, such as "Jon," but not "John" or "Jo."
Ignoring special characters in patterns: In some cases, special characters (like % or _) may appear in the data. If you need to search for them literally, you must escape them using the ESCAPE keyword. For example, to include "100% Cotton" in your search, use SELECT * FROM Products WHERE Name LIKE '%100\% Cotton%' ESCAPE '\';.

Avoid these mistakes to ensure your wildcard search in SQL is accurate and efficient.

Advanced use cases for SQL wildcards

Let's explore more complex SQL wildcard queries. To demonstrate these effectively, we have expanded the demo database with additional records to ensure meaningful results. Due to its size, we cannot provide the full demo database here, but we will share the query results—limited to no more than five records—so you can still follow along and understand the outcome.

Case 1

First, we will combine multiple wildcards (% and _) to find addresses where AddressLine1 starts with "1" immediately followed by exactly two characters before a space, and contains the word "main" later in the string.

SELECT *
FROM Person.Address
WHERE AddressLine1
LIKE '1__ %%main%';

The results include only records that match our conditions, and just three are in the entire database. Note that the LIKE operator is case-insensitive by default in SQL Server, so results containing "Main" appear:

AddressID	AddressLine1	City	StateProvinceID	PostalCode
22448	130 North Main St.	Newcastle	50	2300
12770	130 North Main St.	San Diego	9	92102
11380	165 North Main	Austin	73	78701

Case 2

Say, you need to find cities that start with a vowel (A, E, I, O, U) and do not end with "n" or "s"—you can use [] and [^] to define character ranges and exclusions in your pattern.

SELECT *
FROM Person.Address
WHERE City LIKE '[AEIOU]%'
AND City LIKE '%[^ns]';

In the output, we get just what we wanted, and cities like Edmonds are excluded:

AddressID	AddressLine1	City	StateProvinceID	PostalCode
30	9707 Coldwater Drive	Orlando	15	32804
31	9100 Sheppard Avenue North	Ottawa	57	K4B 1T7
228	9241 St George Dr.	Everett	79	98201
229	3928 San Francisco	Everett	79	98201
230	475 Santa Maria	Everett	79	98201

Case 3

Next, we can combine multiple conditions to narrow search results using wildcards with AND and OR. Let's find addresses in cities starting with "San" or "New" that also have "Drive" in AddressLine1.

SELECT *
FROM Person.Address
WHERE (City LIKE 'San%'
OR City LIKE 'New%')
AND AddressLine1 LIKE '%Drive%';

The result takes into account all our conditions:

AddressID	AddressLine1	City	StateProvinceID	PostalCode
26	5725 Glaze Drive	San Francisco	9	94109
248	1400 Gate Drive	Newport Hills	79	98006
252	9687 Shakespeare Drive	Newport Hills	79	98006
995	4460 Newport Center Drive	Newport Beach	9	92625
11931	7561 Humphrey Drive	Newton	7	V2M1P1

Case 4

You can use wildcards in JOIN clauses. For example, to find all addresses where StateProvinceID matches the StateProvinceID from another table and City contains "tor."

SELECT a.AddressLine1, a.City, s.Name AS StateName, s.TerritoryID
FROM Person.Address a
JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID
WHERE a.City LIKE '%tor%';

The results include quite a bunch, so here are the top five:

AddressID	AddressLine1	City	StateName	TerritoryID
21094	1006 Deercreek Ln	Torrance	California	4
19128	1045 Lolita Drive	Torrance	California	4
15652	1260 Mt. Washington Way	Victoria	British Columbia	6
13224	1315 Norse Drive	Victoria	British Columbia	6
19253	133 Lorie Ln.	Victoria	British Columbia	6

Case 5

In our last use case, we will show how wildcards can be used in JOIN clauses with OR. We will find addresses where either a city contains "Cruz" or a state name (StateProvince.Name) contains "York."

SELECT a.AddressID, a.AddressLine1, a.City, s.Name AS StateName, s.TerritoryID
FROM Person.Address a
JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID
WHERE a.City LIKE '%Cruz%'
OR s.Name LIKE '%York%';

We obtain the JOIN results where the conditions are met:

AddressID	AddressLine1	City	StateName	TerritoryID
27683	1020 Carletto Drive	Santa Cruz	California	4
23031	1065 Almond St.	Santa Cruz	California	4
770	123 Union Square South	New York	New York	2
19172	1583 Westwood Lane	Santa Cruz	California	4
20356	1644 Alicante Court	Santa Cruz	California	4

How to 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.

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