SQL | Wildcard operators

Prerequisite: SQL | WHERE Clause

In the above-mentioned article WHERE Clause is discussed in which the LIKE operator is also explained, where you must have encountered the word wildcards now let’s get deeper into Wildcards. Wildcard operators are used with the LIKE operator, there are four basic operators:

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 table

column: name of field used for filtering data

CREATE Table:

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','xxxxxxxxxx'),
       (2, 'Aman ', 'Chopra', 'Australia','21','xxxxxxxxxx'),
       (3, 'Naveen', 'Tulasi', 'Sri lanka','24','xxxxxxxxxx'),
       (4, 'Aditya', 'Arpan', 'Austria','21','xxxxxxxxxx'),
       (5, 'Nishant. Salchichas S.A.', 'Jain', 'Spain','22','xxxxxxxxxx');
       Select * from Customer;

Output:

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

Query:

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

Output:

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

Query:

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

Output:

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:

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

Query:

SELECT * FROM Customer WHERE CustomerName LIKE 'Nav___';

Output:

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

Query:

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

Output:

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

Query:

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

Output:

7. 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:

8. To fetch records from the Student table with Country containing a total of 7 characters.

Query:

SELECT * FROM Students WHERE Country LIKE '_______';

Output:

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

Query:

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

Output:

Frequently Asked Question

Question: What is a wildcard operator in SQL?

Ans: 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.

This article is contributed by Pratik Agarwal. If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to review-team@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks. Please comment if you find anything incorrect or if you want to share more information about the topic discussed above.