Wildcard Characters

Introduction to Wildcard Characters

A Wildcard Character is a character or a group of characters that can be searched and replaced in a particular given string. Wildcard characters are used with MySQL queries with the LIKE operator. This LIKE operator is used in the where clause of the MySQL query. With where clause we can search for a particular pattern for a particular column of the table.

Example:

% wildcard is used to like %ab will find all ab in a given set of data(records) producing the output like about, above, abundance, absorbent and so on…

Why do we use WildCards?

As you are familiar with MySQL, we have various queries like INSERT, UPDATE, SELECT, DELETE, etc. Since wildcards do the job of searching a particular pattern, the SELECT statement along with the WHERE clause will come into the picture, rather than other MySQL queries. To make the search with wildcards easier, let us look into a simple example of artists and his paintings example. Say, there are different paintings created by different artists available in different regions of the world at different prices. Let the database name be of your choice say “search_artist”

The table name is the artist

Here is  the create a query to create the table ‘artist’

CREATE TABLE `artist` (
`artist_id` int(11) NOT NULL,
`artist_name` varchar(255) NOT NULL,
`artist_painting` varchar(255) NOT NULL,
`artist_country_name` varchar(255) NOT NULL,
`artist_country_code` varchar(255) NOT NULL,
`artist_painting_price` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

After the table is created we will insert the data in the table

Dumping data for table `artist`

INSERT INTO `artist` (`artist_id`, `artist_name`, `artist_painting`, `artist_country_name`, `artist_country_code`, `artist_painting_price`) VALUES
(1, 'Leonardo Da Vinci', 'Mona Lisa', 'Angola', 'AO', '$500'),
(2, 'Edvard Munch', 'The Scream', 'Bahrain', 'BH', '$600'),
(3, 'Sistine Chapel by Michelangelo', 'The Creation of Adam', 'Bangladesh', 'BD', '$700'),
(4, 'Vincent Van Gogh', 'Sun Flowers', 'Malaysia', 'MY', '$550'),
(5, 'Rene Magritte', 'Ceci N’est pas une Pipe', 'Madagascar', 'MG', '$850'),
(6, 'Edvard Munch', 'The Scream', 'Portugal', 'PT', '$750'),
(7, 'Leonardo Da Vinci', 'Mona Lisa', 'Poland', 'PL', '$500'),
(8, 'Claude Monet', 'Poppies in a Field', 'Vietnam', 'VN', '$650'); 

Once you execute the above query the following table will be created

The % percentage

Example One: Now we want to know which all artist who has their painting in the countries where the country name starts with “BA”

The SEARCH query goes like this

SELECT * FROM artist WHERE artist_country_name LIKE 'Ba%'
The result is country names starting with Ba are “Bahrain” and “Bangladesh”

Executing the above query in MySQL

Output of the query

Example Two: Here, we want to know which all artist has their painting in the countries containing “al”

The solution is

SELECT * FROM artist WHERE artist_country_name LIKE '%al%'
The result is country names starting with Ba are “Malaysia” and “Portugal”

Executing the above query in MySQL

Output of the query

In the above two examples, we have single wildcard which is ‘%’

Now we will check for related wildcards like the _ wildcard (the underscore wildcard), the escape keyword, NOT LIKE keyword, etc

Let us look into the second wildcard which is _ underscore wildcard

_ underscore wildcard

Example One: This is also used with the SELECT statement along with where clause where in the  _  stands for any single character, which could be anything

The solution is
SELECT * FROM artist WHERE `artist_painting_price` LIKE '$5_0'
The result is painting price names with this pattern will give output as
$500, $550, $500 in the painting price column

Executing the above query in MySQL

Output of the query

Example Two:

The solution is
SELECT * FROM artist WHERE `artist_country_code` LIKE 'A_’

Executing the above query in MySQL

Output of the above query

Before diving in the third wildcard we will see the combination of the above two wildcards, the combination of ‘_’ and ‘%’ wildcards

Wildcard combination	Meaning
WHERE  artist_painiting LIKE ‘Mo%’	Returns all the values that start with “Mo”
WHERE artist_country_name  LIKE ‘%n’	Returns all the values that end with “n”
WHERE artist_country_name LIKE ‘%da%’	Returns all the values that have “da” in any position
WHERE artist_country_name LIKE ‘_o%’	Returns all the values that have “o” in the second position
WHERE artist_name LIKE ‘L%i’	Returns all the values that start with “L” and ends with “i”

Let us look into the third wildcard which is NOT LIKE wildcard

NOT LIKE wildcard

This NOT LIKE operator like others are used with where clause of the select statement and the underscore and percentage wildcard. It returns the rows from the table where the given pattern does not match.

Example One: We do not want the records where the artist painting has the price of $600 and $650

The solution is  :

SELECT * FROM artist WHERE `artist_painting_price` NOT LIKE '$5%'

Executing the above query in MySQL with Output

The Fourth  Wildcard is  [^characterlist]  with REGEXP

Example One: We want the records that contain all characters which match the pattern present within the bracket.

The Solution is  :
SELECT * FROM artist WHERE `artist_country_name` REGEXP '^[po]'

Here, the artist_country_name containing the group of characters like po results into two records which contains Portugal and Poland only.

Executing the above query in MySQL with Output

The Fifth Wildcard is  ^ NOT with REGEXP keyword

Example: We want the records that contain all characters except those characters which are present within the bracket.

The Solution is  :

SELECT * FROM artist WHERE artist_country_name REGEXP '^[^po]'

Executing the above query in MySQL with Output

The Sixth Wildcard escape keyword

Example One: In this example, the artist’s name is the input which is searched in a table for a given pattern using an escape keyword. It will be more clear with the following example.

The Solution is  :

SELECT * FROM artist WHERE `artist_name` LIKE '%$ Da %' ESCAPE '$'

Executing the above query in MySQL with Output

The Seventh Wildcard ‘|’ keyword to perform a search based on given alphabets.

Example Two: We want the records that whose first names start with character L or S

The Solution is :

SELECT * FROM artist WHERE `artist_painting` REGEXP '^(L|S)'

Executing the above query in MySQL with Output

Conclusion- Wildcard Characters

With this topic we hope, you learned % and _ wildcards with examples. Also, you learn NOT LIKE Operator, the search query with the escape keyword, the [^characterlist]  and others. All this was made clear by performing a SELECT query with a where clause on the “artist” table. Hopefully, each topic got easier to understand and grasp.

Recommended Articles

This has been a guide to Wildcard Characters. Here we have discuss what is wildcard characters, how to use wildcard and how to create a table etc. You can also go through our other suggested articles to learn more –

1. Wildcard in SQL
2. Filters in Tableau
3. Uses of SQL
4. T-SQL String Functions
5. Regex Functions in Python (Example)