A wildcard character is a character which can be used to replace one or more characters in a string. To increase the structure of a structured query language, the two major wild cards “_” and “%” can be used. Wildcards in MySql gives a cushy options to do multi-variant data filtering, lets dive deep in

Photo by Ali Kazal on Unsplash

Like Operator

The LIKE operator permits wildcard characters in MySql. In a WHERE clause, the LIKE operator is used to look for a specific pattern in a column. below are the commonly used wildcards used in the sql

┌────────┬────────────────────────────────────────────────────┐
│ Symbol │ Description │
├────────┼────────────────────────────────────────────────────┤
│ % │ Can symbolize or represent zero or more characters │
│ - │ Stands for a single character │
│ [char] │ Character combinations and ranges to correspond │
│ ^ │ Indicates the next value/range to be ignored │
└────────┴────────────────────────────────────────────────────┘

The wildcards can be clubbed with multiple patterns, below are some.

Let’s Practice

Below is the sample DB, we will be using to understand more about wildcards

Demo Table

The below SQL statement selects all customers whose City begins with the letter “lag”:

SELECT * FROM Customers WHERE City LIKE ‘lag%’ ;

The SQL query below selects all customers who have a City that contains the pattern “os”:

SELECT * FROM Customers WHERE City LIKE ‘%os%’ ;

The SQL query below selects all customers with a City which begins with any character, followed by “agos”

SELECT * FROM Customers WHERE City LIKE “_agos” ;

The following SQL query selects all customers with a City that begins with “B,” then any character, then “r,” then any character, then “in”:

SELECT * FROM Customers WHERE City LIKE ‘B_r_in’ ;

To get all rows from the ‘Contacts’ table with the following condition;

1. The contact_name’ must begin with the letter ‘a’ or ‘m’ or ‘c’

SELECT * FROM Contacts WHERE contact_name LIKE ‘[amc]%’;

2. The ‘contact_name’ must not begin with the letter ‘a’ or ‘m’ or ‘c’,

SELECT * FROM Contacts WHERE contact_name LIKE ‘[^amc]%’;

Note: `^` can be interchanged with `!` as well and still does the same job

Wildcards helps us in filtering the data and can be used in multiple ways to optimise the SQL performance, Do share your thoughts, Cheers!