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 particularly 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. We can search for a particular pattern for a particular column of the table with the where clause.

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.’

Code:

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.`

Code:

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.

1. 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:

Code:

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:

Code:

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 a single wildcard, which is ‘%.’

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

2. _ 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:

Code:

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:

Code:

SELECT * FROM artist WHERE `artist_country_code` LIKE 'A_’

Executing the above query in MySQL:

Output of the above query:

Before diving into 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.”

3. NOT LIKE Wildcard

This NOT LIKE operator, like others, are used with the 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:

Code:

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

Executing the above query in MySQL with Output:

4. [^characterlist] with REGEXP

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

The solution is:

Code:

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:

5. ^ NOT with REGEXP keyword

Example: We want the records containing all characters except those present within the bracket.

The solution is:

Code:

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

Executing the above query in MySQL with output:

6. Escape Keyword

Example One: In this example, the artist’s name is the input that 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:

Code:

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

Executing the above query in MySQL with Output.

7. ‘|’ keyword to perform a search based on given alphabets

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

The solution is:

Code:

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

Executing the above query in MySQL with Output:

Conclusion – Wildcard Characters

With this topic you saw % and _ wildcards with examples. Also, you saw NOT to 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 discussed what wildcard characters are, 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