Create Tables Show Tables Alter Tables Rename Tables Clone Tables Truncate Tables Temporary Tables Repair Tables Describe Tables Add/Delete Columns Show Columns Rename Columns Table Locking Drop Tables Derived Tables

Queries Constraints Insert Query Select Query Update Query Delete Query Replace Query Insert Ignore Insert on Duplicate Key Update Insert Into Select

Create Views Update Views Drop Views Rename Views

Indexes Create Index Drop Index Show Indexes Unique Index Clustered Index Non-Clustered Index

Using Joins Inner Join Left Join Right Join Cross Join Full Join Self Join Delete Join Update Join Union vs Join

Unique Key Primary Key Foreign Key Composite Key Alternate Key

Triggers Create Trigger Show Trigger Drop Trigger Before Insert Trigger After Insert Trigger Before Update Trigger After Update Trigger Before Delete Trigger After Delete Trigger

Data Types VARCHAR BOOLEAN ENUM DECIMAL INT FLOAT BIT TINYINT BLOB SET

Regular Expressions RLIKE Operator NOT LIKE Operator NOT REGEXP Operator regexp_instr() Function regexp_like() Function regexp_replace() Function regexp_substr() Function

Fulltext Search Natural Language Fulltext Search Boolean Fulltext Search Query Expansion Fulltext Search ngram Fulltext Parser

Date and Time Functions Arithmetic Operators Numeric Functions String Functions Aggregate Functions

Useful Functions Statements Reference Quick Guide Useful Resources Discussion

Feedback

Submit request if you have any questions.

Course

Wildcards

MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

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
﻿
﻿
﻿
﻿
﻿
﻿

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'

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.

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Wildcards

The MySQL wildcards

Syntax

The MySQL Percent % Wildcard

Example

Output

Example

Output

Example

Output

Example

Output

The MySQL Underscore _ Wildcard

Example

Output

Example

Output

Example

Output

Example

Output

Example

Output