SQL Wildcard – Finding Matches and Patterns in Data

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.