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”
4.5 (4,943 ratings)
View Course
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 discussed 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 –
