Open In App

SQL | Wildcard operators

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

Wildcard operators are used with the LIKE operator, which is generally used to search the data in the database and there are four basic operators %, _,-,[range_of_chracters]. Let us explain all these operators in brief –

Operator Table

Operator Description
% It is used in substitute of zero or more characters.
_ It is used as a substitute for one character.
It is used to substitute a range of characters.
[range_of_characters] It is used to fetch a matching set or range of characters specified inside the brackets.

Syntax

SELECT column1,column2 FROM table_name 

WHERE column LIKE wildcard_operator;

column1,column2: fields in the table

table_name: name of the table

column: name of the field used for filtering data

CREATE Table Customer

CREATE TABLE Customer(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
LastName VARCHAR(50),
Country VARCHAR(50),
Age int(2),
Phone int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Customer (CustomerID, CustomerName, LastName, Country, Age, Phone)
VALUES (1, 'Shubham', 'Thakur', 'India','23','9125368745'),
(2, 'Aman ', 'Chopra', 'Australia','21','9632784152'),
(3, 'Naveen', 'Tulasi', 'Sri lanka','24','8965741538'),
(4, 'Aditya', 'Arpan', 'Austria','21','9874589736'),
(5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','8754128965');
Select * from Customer;

Output

Customer Table

Customer Table

Using the % Wildcard

1. To fetch records from the Customer table with NAME starting with the letter ‘A’.

Query

 SELECT * FROM Customer WHERE CustomerName LIKE 'A%';

Output

 Table-1

Table-1

2. To fetch records from the Customer table with NAME ending with the letter ‘A’.

Query

SELECT * FROM Customer WHERE CustomerName LIKE '%A';

Output

 Table - 2

Table – 2

3. To fetch records from the Customer table with NAME with the letter ‘A’ at any position.

Query

SELECT * FROM Customer WHERE CustomerName LIKE '%A%';

Output

 Table - 3

Table -3

4. To fetch records from the Customer table the LastNamecontaining ‘ra’ at any position, and the result set should not contain duplicate data.

Query

SELECT DISTINCT * FROM Customer WHERE Country LIKE '%ra%';

Output

Table - 4

Table – 4

Using the _ Wildcard

1. To fetch records from the Customer table with NAME ending any letter but starting from ‘Nav’.

Query

SELECT * FROM Customer WHERE CustomerName LIKE 'Nav___';

Output

Customer Table

Table – 5

2. To fetch records from the Customer table with Country containing a total of 7 characters.

Query

SELECT * FROM Customer WHERE Country LIKE '_______';

Output

 Table - 6

Table – 6

Using the [Charlist] Wildcard

1. To fetch records from the Customer table with LastName containing letters ‘a, ‘b’, or ‘c’.

Query

SELECT * FROM Customer WHERE LastName REGEXP '[A-C]';

Output

 table - 7

Table – 7

2. To fetch records from the Customer table with LastName not containing letters ‘y’, or ‘z’.

Query

SELECT * FROM Students WHERE LastName NOT LIKE '%[y-z]%';

Output

Table - 8

Table – 8

Using Both % and _ Wildcard

1. To fetch records from the Student table with the PHONE field having an ‘8’ in the 1st position and a ‘3’ in the 3rd position.

Query

SELECT * FROM Student WHERE PHONE LIKE '8__3%';

Output

 table -9

Table – 9

FAQs on WildCard Operator

Q 1: What is a wildcard operator in SQL?

Answer:

The LIKE operator makes use of wildcard characters. The LIKE operator is used in a WHERE clause to look for a specific pattern in a column.


Last Updated : 25 Aug, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads