PL/SQL LIKE Operator

The PL/SQL LIKE operator is a powerful tool used in SQL queries to search for patterns in character data. It allows you to match strings based on specific patterns defined by wildcards. This operator is commonly used in SELECT, UPDATE, and DELETE statements to filter records based on partial or complex string matches.

In this article, we will explain PL/SQL LIKE Operator with its syntax, examples and output in detail.

PL/SQL LIKE Operator

The LIKE operator is particularly useful when you need to find data that matches a certain pattern but does not know the exact value. The PL/SQL LIKE operator is used to match a string against a pattern. The pattern can include two wildcards:

• %: Represents zero or more characters.

• _: Represents a single character.

Syntax

SELECT column_name(s)

FROM table_name

WHERE column_name LIKE pattern;

Key Terms

• column_name(s): Specifies the column(s) that you want to retrieve.

• table_name: Indicates the table from which you want to fetch the data.

• pattern: Defines the string pattern you want to match using the LIKE operator. This pattern can include wildcards such as % and _.

PL/SQL LIKE Operator Examples

We'll create two tables, Products and Suppliers, to demonstrate how the LIKE operator can be used for pattern matching in SQL queries. The LIKE operator allows us to search for specific patterns within text data, making it useful for filtering records based on partial matches or specific formats.

1. Products Table

The Products table is created with columns ProductID, ProductName, and Category. The ProductID is the primary key. Data is inserted into tables. The Products table includes various items under different categories.

Query:

CREATE TABLE Products (
 ProductID NUMBER PRIMARY KEY,
 ProductName VARCHAR2(50),
 Category VARCHAR2(30)
);

INSERT INTO Products (ProductID, ProductName, Category)
VALUES (1, 'Laptop', 'Electronics');

INSERT INTO Products (ProductID, ProductName, Category)
VALUES (2, 'Smartphone', 'Electronics');

INSERT INTO Products (ProductID, ProductName, Category)
VALUES (3, 'Tablet', 'Electronics');

INSERT INTO Products (ProductID, ProductName, Category)
VALUES (4, 'Desk Chair', 'Furniture');

INSERT INTO Products (ProductID, ProductName, Category)
VALUES (5, 'Dining Table', 'Furniture');

Output:

Explanation:

• The Products table is populated with five records, each representing a different product category.
  
• The table includes a ProductID for unique identification, a ProductName to specify the item, and a Category to classify the product.
  
• The output displays products across two categories: Electronics and Furniture, showing their respective IDs and names.

2. Suppliers Table

The Suppliers table is created with columns SupplierID, SupplierName, and ContactName. The SupplierID is the primary key. Data is inserted into tables. Suppliers table contains supplier information.

Query:

CREATE TABLE Suppliers (
 SupplierID NUMBER PRIMARY KEY,
 SupplierName VARCHAR2(50),
 ContactName VARCHAR2(50)
);

INSERT INTO Suppliers (SupplierID, SupplierName, ContactName)
VALUES (1, 'TechC', 'John Doe');

INSERT INTO Suppliers (SupplierID, SupplierName, ContactName)
VALUES (2, 'FurniCo', 'Jane Smith');

INSERT INTO Suppliers (SupplierID, SupplierName, ContactName)
VALUES (3, 'GadgetWorld', 'Alice Johnson');

INSERT INTO Suppliers (SupplierID, SupplierName, ContactName)
VALUES (4, 'ElectroMart', 'Bob Brown');

Output:

Explanation:

• The Suppliers table includes four records, each representing a different supplier.
  
• Each record contains a SupplierID for unique identification, a SupplierName to identify the supplier, and a ContactName for the primary contact person.
  
• The output lists the suppliers along with their IDs and contact names, allowing easy identification of suppliers for various products.

Example 1: Using % Wildcard

The % wildcard matches zero or more characters in a string. Let's find all products whose names start with 'D'.

The query retrieves all products from the Products table where the ProductName starts with the letter 'D'. The % wildcard after 'D' allows for any number of characters following 'D'.

Query:

SELECT ProductName, Category
FROM Products
WHERE ProductName LIKE 'D%';

Output:

Explanation:

• The query uses the LIKE operator with the pattern 'D%' to find products where the ProductName starts with the letter "D".
  
• The output includes products that match this criterion, specifically "Desk Chair" and "Dining Table", both of which fall under the Furniture category.

Example 2: Using _ Wildcard

The _ wildcard matches exactly one character. Let's find all suppliers whose names are five characters long.

The query retrieves all suppliers from the Suppliers table where the SupplierName has exactly five characters. Each underscore _ represents a single character.

Query:

SELECT SupplierName, ContactName
FROM Suppliers
WHERE SupplierName LIKE '_____';

Output:

Explanation:

• The query uses the LIKE operator with the pattern '_____' to find suppliers whose SupplierName consists of exactly five characters.
  
• The output shows "TechC" as the only supplier matching this criterion, with "John Doe" as the contact person.

Example 3: Using NOT Operator

The NOT operator can be combined with LIKE to find records that do not match a pattern. Let's find all products that do not belong to the Electronics category.

The query retrieves all products from the Products table where the Category is not 'Electronics'. The NOT operator negates the LIKE condition.

Query:

SELECT ProductName, Category
FROM Products
WHERE Category NOT LIKE 'Electronics';

Output:

Explanation:

• The query uses the NOT LIKEoperator to filter out products in the Electronics category, displaying only those in other categories.
  
• The output lists "Desk Chair" and "Dining Table" from the Furniture category, as they do not match the excluded category criterion.

Example 4: Using Escape Characters

Sometimes we need to search for patterns that include the % or _ characters. We can use the escape character to treat them as literals. Let's find all suppliers whose names contain 'Tech_'.

The query retrieves all suppliers from the Suppliers table where the SupplierName contains 'Tech_' as part of the name. The \ is used as an escape character to treat the underscore _ as a literal character.

Query:

SELECT SupplierName, ContactName
FROM Suppliers
WHERE SupplierName LIKE 'Tech_%' ESCAPE '\';

Output:

Explanation:

• The query uses the LIKE operator with the pattern 'Tech_%' and specifies an escape character '\ to handle special characters.
  
• It filters for suppliers whose names start with "Tech" followed by any single character, and lists "TechC" as a matching result.
  
• If there were suppliers like "TechCorp", they would also be included in the output.

Conclusion

The PL/SQL LIKE operator is an essential tool for pattern matching in Pl/SQL queries. It provides flexibility in searching for specific data using wildcards and can be combined with other operators to refine search criteria.

Understanding how to use the LIKE operator effectively can significantly enhance your ability to query databases.