Khóa học miễn phí MySQL – Wildcards nhận dự án làm có lương

MySQL – Wildcards

Table of content


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

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc