MySQL - Wildcards

---

---

The MySQL wildcards

The MySQL wildcards are special characters used in combination with the LIKE operator to search for text patterns in a table column. MySQL provides two wildcards: percent (%) and underscore (_).

The following table mentions the use case of the two wildcards in MySQL −

S.NO	Wildcard & Description
1	The percent (%) Matches zero or one characters'. For example, 'a%' matches strings starting with 'a' like 'android' or 'aeroplane'.
2	The underscore (_) Matches a single character. For instance, '_un' matches three-character strings ending with 'un' like 'gun' or 'bun'

Syntax

Following is the syntax of % and _ wildcards in MySQL −

SELECT * FROM table_name
WHERE column_name LIKE wildcard;

The wildcard characters can be used in combination with each other. The following table demonstrates different ways of using '%' and '_' with the LIKE operator in a WHERE clause −

S.NO	Statement & Description
1	WHERE SALARY LIKE '200%' Finds any values that start with 200.
2	WHERE SALARY LIKE '%200%' Finds any values that have 200 in any position.
3	WHERE SALARY LIKE '_00%' Finds any values that have 00 in the second and third positions.
4	WHERE SALARY LIKE '2_%_%' Finds any values that start with 2 and are at least 3 characters in length.
5	WHERE SALARY LIKE '%2' Finds any values that end with 2.
6	WHERE SALARY LIKE '2%0' Finds any value that starts with 2 and ends with 0.
7	WHERE SALARY LIKE '_2%3' Finds any values that have a 2 in the second position and end with a 3.
8	WHERE SALARY LIKE '2___3' Finds any values in a five-digit number that start with 2 and end with 3.

The MySQL Percent % Wildcard

The MySQL % wildcard is a symbol used in SQL queries for pattern matching. It represents any sequence of characters (including zero characters) within a string.

When used with the LIKE operator in a WHERE clause, % allows you to search for values that match a specified pattern.

Example

First, let us create a table with the name CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR(25) NOT NULL,
   SALARY DECIMAL(18, 2),
   PRIMARY KEY(ID)
);

Now, let us insert values into the table created above using the INSERT statement as shown below −

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

The CUSTOMERS table obtained is as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Here, we are retrieving all the records from the CUSTOMERS table where SALARY starts with 2000 −

SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '2000%';

Output

The output of the above query is as given below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00

Example

In the following query, we are fetching all the records where ADDRESS starts with 'D' and ends with 'i' −

SELECT * FROM CUSTOMERS
WHERE ADDRESS LIKE 'D%i';

Output

On executing the given query, the output is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00

Example

Here, we are finding all the records where ADDRESS ends with 'd' −

SELECT * FROM CUSTOMERS
WHERE ADDRESS LIKE '%d';

Output

When we execute the above query, the output is obtained as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
6	Komal	22	Hyderabad	4500.00

Example

In the following query, we are trying to fetch all the records where SALARY has '1' in any position −

SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '%1%';

Output

The output produced from the above query is as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
7	Muffy	24	Indore	10000.00

The MySQL Underscore _ Wildcard

The MySQL underscore Wildcard represents a single character at the position where it is used. When combined with the LIKE operator in a WHERE clause, the underscore wildcard allows you to search for values that match a specific pattern with a single character placeholder.

Example

Here, we are retrieving all the CUSTOMERS with NAME starting with a character, followed by 'ardik' −

SELECT * FROM CUSTOMERS
WHERE NAME LIKE '_ardik';

Output

Let us compile and run the query, to produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
5	Hardik	27	Bhopal	8500.00

Example

Now, we are retrieving all CUSTOMERS with NAME starting with 'M', followed by any character, followed by 'f', followed by any character, followed by 'y' −

SELECT * FROM CUSTOMERS
WHERE NAME LIKE 'M_f_y';

Output

When we execute the above query, the output is obtained as follows −

ID	NAME	AGE	ADDRESS	SALARY
7	Muffy	24	Indore	10000.00

Example

In the below query, we are retrieving all the records where SALARY have '500' in the second, third, and fourth positions −

SELECT * FROM CUSTOMERS
WHERE SALARY LIKE '_500%';

Output

On executing the given query, the output is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00

Example

In the following query, we are retrieving all the records where ADDRESS starts with 'M' and is at least 3 characters in length −

SELECT * FROM CUSTOMERS
WHERE ADDRESS LIKE 'M_%_%';

Output

The output of the above query is produced as given below −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00

Example

The following query retrieves all records where NAME has 'h' in the second position and ends with 'i' −

SELECT * FROM CUSTOMERS
WHERE NAME LIKE '_h%i';

Output

If we compile and run the query, the result is produced as follows −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00