Udemy logo

sql wildcardWhen you want to find data that meets a specific criteria or to find patterns in data, then you can use a wildcard query. In SQL, a wildcard character is used along with a statement, such as a SELECT statement, in order to retrieve data determined by a pattern. For instance, if you wanted to find all of the cities in a table that contain the pattern “ville”, using a wildcard would enable you to do so. There are different wildcard characters that you have the option to use and the usage will be dependent on the purpose of the search as well as the type of data you intend to gather.

Discovering SQL queries and want to learn more? Take a class on Udemy.

Wildcard Characters

As with many SQL objects, Oracle and Access use different options for wildcard characters.

Oracle uses the following symbols for wildcards:

% (The percent sign is used for finding patterns with strings)

_ (The underscore is used for finding a pattern with one character; more than one character can be included for search criteria)

\* , \_ (A backslash along with another character allows the character proceeding the backslash to be treated as a literal; you will have to set up an escape in order to use this technique)

Access uses the following symbols for wildcards:

* (The asterisk is used for finding patterns with strings)

? (The question mark is used for finding a pattern with one character; more than one character can be included for search criteria)

[*] [?] (Square brackets allow a character within the brackets be treated as a literal)

# (The Pound sign is used for finding a specific digit)

[a-z] (Square brackets with two characters separated by a comma within the brackets are used for finding patterns with ranges)

Examples of Using Wildcards

Scenario 1

In your first example, you have a company that has conducted a survey in which survey takers had entered the name of the town or city in which they reside. You would like to search for cities or towns entered that include the pattern “ville”. The following is the table, named Survey_Tbl, which you will initially use:

SURVEY_ID SURVEY_CITY

1452 Bay Point

1468 Cayey

1489 Keokuk

1463 Sandia Knolls

1452 Waynesville

1476 Sparta

1421 Forest River

1411 Duncan Falls

1451 Sunnyvale

1434 Davenport

1458 Clarkesville

1428 Mineral

1542 Myerstown

1578 Mocksville

1563 Pattonsburg

1598 Sand Lake

1563 Maplewood

1542 Norton Shores

1572 Reedsville

1516 North Bellmore

1584 Hyattsville

1621 Windsor Place

1656 Bee Ridge

1642 Camptonville

1682 Johnston City

1625 Niceville

1721 Springport

1742 Knightdale

1765 Waldenburg

1795 Troutville

1799 East Greenville

1821 Reinbeck

1874 Asheville

1863 June Park

1846 Pine Valley

In order to find which cities or towns include the letters “ville” (in that order), you could use a query that includes an appropriate wildcard character along with the LIKE condition, such as the following:

SELECT
SURVEY_ID,
SURVEY_CITY
FROM Survey_Tbl
WHERE
SURVEY_CITY
LIKE
‘%ville%’;

In Access, the query would be as follows:

SELECT
SURVEY_ID,
SURVEY_CITY
FROM Survey_Tbl
WHERE
SURVEY_CITY
LIKE
‘*VILLE*’;

SURVEY_ID SURVEY_CITY

1452 Waynesville

1458 Clarkesville

1578 Mocksville

1572 Reedsville

1584 Hyattsville

1642 Camptonville

1625 Niceville

1795 Troutville

1799 East Greenville

1874 Asheville

Note that I had entered the string being searched for as “ville” for the Oracle example; this had been due to case sensitivity reasons. If a database has been set to be case-sensitive, then letters being either lower case or upper case is a significant factor. In the example above, if I would have searched for “VILLE”, then the query would not have been able to produce successful results. You can either be mindful of the case for data you are searching for, you can use other options that allow you to search without having to know the case type of data in tables, or you can deactivate case-sensitivity for a database.

For Oracle SQL, in some cases, you can use the following command to set all data to have a case type of uppercase before additional commands are run:

SET SQLCASE UPPER;

Interested in gaining more skills in Oracle SQL? Take a Udemy class.

Scenario 2

In your second scenario, you have been tasked to find all entries that use wheat flour in a table being used for a bakery. As stated earlier, more than one character may be used for a wildcard query using the underscore character. The underscores in a wildcard query can be used for trying to match any characters and can be preceded or proceeded by a single character. The following is the table that you will be working with:

Select * from Flour_Tbl;

RECIPE_ID RECIPE_NAME FLOUR_TYPE

41234 Vanilla Cake All-Purpose

41235 Nutty Bread Wheat

41236 Pretzels Bread

41237 Chocolate Cake All-Purpose

41238 Date Squares Pastry

41239 Quick Bread Self-Rising

41240 Cheese Biscuits All-Purpose

41241 Tea Biscuits All-Purpose

41242 Pistachio Muffins All-Purpose

41243 Wheat Pancakes Wheat

41244 Pizza Crust Bread

41245 Carrot Cake Wheat

41246 Pastry Shells Pastry

41247 Blackberry Scones Wheat

You could use a query that searches for entries that have the pattern, “W_eat ”, in the FLOUR_TYPE field; such as the following:

SELECT
RECIPE_ID,
RECIPE_NAME,
FLOUR_TYPE
FROM
Flour_Tbl
WHERE
FLOUR_TYPE
LIKE
'W_eat';

In Access, the query would use a question mark (?) as opposed to an underscore (_).

RECIPE_ID RECIPE_NAME FLOUR_TYPE

41235 Nutty Bread Wheat

41243 Wheat Pancakes Wheat

41245 Carrot Cake Wheat

41247 Blackberry Scones Wheat

Similarly, to further demonstrate the use of the underscore wildcard character, you could use the following query to find all recipe names that start with the letter “W”:

SELECT
RECIPE_ID,
RECIPE_NAME,
FLOUR_TYPE
FROM
Flour_Tbl
WHERE
RECIPE_NAME
LIKE
'W_%';

RECIPE_ID RECIPE_NAME FLOUR_TYPE

41243 Wheat Pancakes Wheat

Scenario 3

In this scenario, your goal is to find values from the FLOUR_TYPE column, from the Flour_Tbl table, that do not have a value of “Wheat”. In this case, you could use a query that includes the arguments NOT and LIKE. An example of a wildcard query that could be used to exclude values is as follows:

SELECT
RECIPE_ID,
RECIPE_NAME,
FLOUR_TYPE
FROM
Flour_Tbl
WHERE
FLOUR_TYPE
NOT
LIKE
'W_%';

RECIPE_ID RECIPE_NAME FLOUR_TYPE

41234 Vanilla Cake All-Purpose

41236 Pretzels Bread

41237 Chocolate Cake All-Purpose

41238 Date Squares Pastry

41239 Quick Bread Self-Rising

41240 Cheese Biscuits All-Purpose

41241 Tea Biscuits All-Purpose

41242 Pistachio Muffins All-Purpose

41244 Pizza Crust Bread

41246 Pastry Shells Pastry

Would you like to learn more about Oracle SQL by using examples? Take a Udemy class.

Conclusion

Wildcard characters can provide flexibility with queries as there is a wide array of combinations that could be used for queries. Wildcard queries can help with finding values that meet a specific criteria, excluding values, searching for ranges, etc. Searching for specific characters and other values in a database would be a much more tedious task without options such as wildcard queries.

Page Last Updated: May 2014

Top courses in SQL

Complete SQL and Databases Bootcamp: Zero to Mastery [2023]
Andrei Neagoie, Mo Binni, Zero To Mastery
4.6 (3,718)
SQL and PostgreSQL: The Complete Developer's Guide
Stephen Grider
4.7 (5,461)
Highest Rated
SQL - The Complete Developer's Guide (MySQL, PostgreSQL)
Academind by Maximilian Schwarzmüller, Maximilian Schwarzmüller, Manuel Lorenz
4.7 (1,032)
101 Practice SQL Questions: Basic to Advanced
Curtis Norman
4.9 (20)
Highest Rated
The Complete SQL Bootcamp 2022: Go from Zero to Hero
Jose Portilla
4.7 (155,400)
Bestseller
SQL for Beginners: The Easiest Way to Learn SQL Step by Step
Code Star Academy, Oracle Master Training | 175,000+ Students Worldwide
4.5 (3,422)
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (75,480)
Bestseller
SQL for Beginners: Learn SQL using MySQL and Database Design
Tim Buchalka's Learn Programming Academy, Jean-Paul Roberts
4.5 (39,927)
SQL Programming Basics
Global Academy
4.5 (3,906)

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.

Request a demo