Wildcards in sql

Feb 4, 2022 - 18:56

0 23

What are wildcards?

Wildcards are symbols that help you search for data that matches complex criteria. Wildcards are used in conjunction with the LIKE comparison operator or the NOT LIKE comparison operator.

Why use WildCards?

If you are familiar with using SQL, you might think that you can search for any complex data using SELECT and WHERE clauses. Then why use Wildcards?

Before answering this question, let's look at an example. Let's say the Myflix video library's marketing department is running a marketing campaign in the city of Texas and would like some feedback on the number of attendees.

registered in Texas, you can use the following SELECT statement along with the WHERE clause to get the information you need.

SELECT * FROM members WHERE postal_address = 'Austin , TX' OR postal_address = Dallas , TX OR postal_address = Iola,TX OR postal_adress = Houston ,TX';

As you can see from the query above, the "WHERE clause" gets complicated. However, using wildcards simplifies the query as we can use something as simple as the script shown below.

SELECT * FROM members WHERE postal_address like '%TX';

In short, wildcards allow us to turn powerful search engines into our data-driven applications.

Wildcard types

% percent

The % percent character is used to specify a pattern of zero (0) or more characters . It has the following basic syntax.

SELECT statements... WHERE fieldname LIKE 'xxx%';

HERE

"SELECT statement..." is a standard SQL SELECT command.
"WHERE" is the keyword used to apply the filter.
"LIKE" is a comparison operator that is used in conjunction with wildcards
"xxx" is any given start pattern, such as one character or more, and "%" matches any number of characters starting at zero (0).

To fully appreciate the above statement, let's look at a practical example

Let's say we want to get all movies that have the word "code" in their title, we'll use a percent wildcard to pattern match on both sides of the word "code". Following is the SQL statement that can be used to achieve the desired results.

SELECT * FROM movies WHERE title LIKE '%code%';

Running the above script in MySQL Workbench against myflixdb gives us the results shown below.

movie_id	title	director	year_released	category_id
4	Code Name Black	Edgar Jimz	2010	NULL
7	Davinci Code	NULL	NULL	6

Note that even if the search keyword "code" appears at the beginning or end of a title, it is still returned in our result set. This is because our code includes any number of characters at the beginning and then matches the pattern "code" followed by any number of characters at the end.

Let's now modify our script above to only include the percent wildcard at the beginning of the search term.

SELECT * FROM movies WHERE title LIKE '%code';

Running the above script in MySQL Workbench against myflixdb gives us the results shown below.

movie_id	title	director	year_released	category_id
7	Davinci Code	NULL	NULL	6

Note that only one record was returned from the database. This is because our code matches any number of characters at the beginning of the movie title and only gets entries that end with the pattern "code".

Let's now shift the percent symbol to the end of the specified pattern to be matched. The modified script is shown below.

SELECT * FROM movies WHERE title LIKE 'code%';

Running the above script in MySQL Workbench against myflixdb gives us the results shown below.

movie_id	title	director	year_released	category_id
4	Code Name Black	Edgar Jimz	2010	NULL

Note that only one record was returned from the database. This is because our code matches all headers that start with the pattern "code" followed by any number of characters.

_ Underscore wildcard

The wildcard wildcard is used to match exactly one character . Let's say we want to find all movies released in 200x, where x is exactly one character, which can be any value. For this we would use the underscore character. Scenario below select all films that were released in the year "200x"

SELECT * FROM movies WHERE year_released LIKE '200_';

Running the above script in MySQL Workbench against myflixdb gives us the results shown below.

movie_id	title	director	year_released	category_id
2	Forgetting Sarah Marshall	Nicholas Stoller	2008	2
9	honey mooners	John Shultz	2005	eight

Note that only movies that have 200 characters in the release year field were returned in our result set. This is because the underscore matches the pattern 200 followed by any single character

Not like

The logical NOT operator can be used along with wildcards to return strings that do not match the specified pattern.

Let's say we want to get movies that weren't released in 200x. We would use the logical NOT operator along with the underscore to get our results. Below is a script that does this.

SELECT * FROM movies WHERE year_released NOT LIKE '200_';

movie_id	title	director	year_released	category_id
one	Pirates of the Caribbean 4	Rob Marshall	2011	one
4	Code Name Black	Edgar Jimz	2010	NULL
eight	Underworld-Awakeninh	Michael Eal	2012	6

Note that in our result set, only movies that do not start with the year 200 were returned. This is because we used the logical NOT operator in our pattern matching.

escape keyword

The ESCAPE keyword is used to escape pattern matching characters such as percentage (%) and underscore (_) if they form part of the data.

Let's assume we want to test the string "67%" which we can use;

LIKE '67#%%' ESCAPE '#';

If we want to find the movie "67% Guilty", we can use the script shown below to do so.

SELECT * FROM movies WHERE title LIKE '67#%%' ESCAPE '#';

Notice the double " %% " in the LIKE clause, the first in red " % " is treated as part of the string to be found. The other is used to match any number of the following characters.

The same query will also work if we use something like

SELECT * FROM movies WHERE title LIKE '67=%%' ESCAPE '=';

Summary

Like & Wildcards are powerful tools that help you search for data that matches complex patterns.
There are several wildcards which include percentage, underscore and charlist (not supported by MySQL) among others
The percent wildcard is used to match any number of characters, starting with zero (0) or more.
The wildcard wildcard is used to match exactly one character.