close
The Wayback Machine - https://web.archive.org/web/20250102130841/https://www.geeksforgeeks.org/sql-wildcard-operators/
Open In App

SQL Wildcard Characters

Last Updated : 07 Aug, 2024
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Save
Share
Report
News Follow

SQL wildcard characters are special symbols used in SQL queries to search for specific patterns within string data. These characters are particularly effective in the LIKE and NOT LIKE clauses, allowing for filtering results based on partial matches.

In this article, We will learn about SQL Wildcard Characters by understanding the different Wildcard Characters with the examples and so on.

SQL Wildcard Characters

  • SQL wildcard Characters are special characters used in SQL queries to search for patterns within string data.
  • These operators are particularly useful in the LIKE and NOT LIKE clauses for filtering results based on partial matches.

Wildcard Characters

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;

Explanation:

  • 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

Conclusion

SQL wildcard characters enhance the versatility of SQL queries, enabling the extraction of data based on partial and pattern-based criteria. By using %, _, and other wildcards, users can efficiently query complex datasets, making SQL a powerful tool for data analysis and manipulation.

FAQs on WildCard Operator

What are SQL wildcard characters?

SQL wildcard characters are special symbols used with the LIKE operator to search for patterns in text data.

Which 2 wildcards are used in SQL?

The two main SQL wildcards are % (matches any sequence of characters) and _ (matches a single character).

What is LIKE '%[0-9]%' in SQL?

In SQL Server, LIKE '%[0-9]%' finds records containing any digit from 0 to 9.



Next Article

Similar Reads

three90RightbarBannerImg