SQL LIKE Operator

SQL LIKE operator is used with the WHERE clause to search for a specified pattern in a column. LIKE operator finds and returns the rows that fit in the given pattern.

LIKE operator is case-insensitive by default in most database systems. This means that if you search for “apple” using the LIKE operator, it will return results that include “Apple”, “APPLE”, “aPpLe”, and so on.

For making the LIKE operator case-sensitive, you can use the “BINARY” keyword in MySQL or the “COLLATE” keyword in other database systems. 

For example:

SELECT * FROM products WHERE name LIKE BINARY 'apple%'

This following query will only return products whose name starts with “apple” and is spelled exactly like that, without capital letters.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

LIKE Operator Wild Cards

Wild cards are used with the LIKE operator to search for specific patterns in strings. Wild card characters substitute one or more characters in the string. There are four wildcard characters in SQL:

1. % (Percent): Represents zero or more characters.
2. _ (Underscore): Represents a single character.
3. [] (Square Brackets): Represents any single character within brackets.
4. – (Hyphen): Specify a range of characters inside brackets.

Wild Cards Examples

The below table shows some examples on how wild card can be written and what do they mean:

Pattern	Meaning
‘a%’	Match strings that start with ‘a’
‘%a’	Match strings with end with ‘a’
‘a%t’	Match strings that contain the start with ‘a’ and end with ‘t’.
‘%wow%’	Match strings that contain the substring ‘wow’ in them at any position.
‘_wow%’	Match strings that contain the substring ‘wow’ in them at the second position.
‘_a%’	Match strings that contain ‘a’ at the second position.
‘a_ _%’	Match strings that start with ‘a and contain at least 2 more characters.

Demo SQL Database

In this tutorial on SQL LIKE Operator, we will use the following table in the examples.

Supplier Table

SupplierID	Name	Address
S1	Paragon Suppliers	21-3, Okhla, Delhi
S2	Mango Nation	21, Faridabad, Haryana
S3	Canadian Biz	6/7, Okhla Phase II, Delhi
S4	Caravan Traders	2-A, Pitampura, Delhi
S5	Harish and Sons	Gurgaon, NCR
S6	Om Suppliers	2/1, Faridabad, Haryana

To create this table on your system, write the following SQL queries:

MySQL

CREATE TABLE Supplier (
    SupplierID CHAR(2) PRIMARY KEY,
    Name VARCHAR(50),
    Address VARCHAR(100)
);
INSERT INTO Supplier (SupplierID, Name, Address)
VALUES
    ('S1', 'Paragon Suppliers', '21-3, Okhla, Delhi'),
    ('S2', 'Mango Nation', '21, Faridabad, Haryana'),
    ('S3', 'Canadian Biz', '6/7, Okhla Phase II, Delhi'),
    ('S4', 'Caravan Traders', '2-A, Pitampura, Delhi'),
    ('S5', 'Harish and Sons', 'Gurgaon, NCR'),
    ('S6', 'Om Suppliers', '2/1, Faridabad, Haryana');

SQL LIKE Examples

Let’s look at some examples of LIKE operator in SQL, and understand it’s working.

Example 1

Retrieve SupplierID, Name, and Address from suppliers table, where supplier name starts form k.

SELECT SupplierID, Name, Address
FROM Suppliers
WHERE Name LIKE 'Ca%';

Output:

S3	Canadian Biz	6/7, Okhla Phase II, Delhi
S4	Caravan Traders	2-A, Pitampura, Delhi

Example 2

Retrieve entire table, where address contains OKHLA.

SELECT *
FROM Suppliers
WHERE Address LIKE '%Okhla%';

Output:

S1	Paragon Suppliers	21-3, Okhla, Delhi
S3	Canadian Biz	6/7, Okhla Phase II, Delhi

Example 3

Retrieve SupplierID, Name and Address of supplier whose name contains “ango” in second substring.

SELECT SupplierID, Name, Address
FROM Suppliers
WHERE Name LIKE '_ango%';

Output:

S2	Mango Nation	21, Faridabad, Haryana

SQL LIKE Application

The LIKE operator is extremely resourceful in situations such as address filtering wherein we know only a segment or a portion of the entire address (such as locality or city) and would like to retrieve results based on that. The wildcards can be resourcefully exploited to yield even better and more filtered tuples based on the requirement. 

Key Takeaways About LIKE Operator

• LIKE operator is used to search for specific patterns in a column.
• It is mostly used with WHERE clause for finding or filtering specific data.
• Like Operator is case-insensitive by default, to make it case sensitive, we can use BINARY keyword.
• LIKE operator has 4 wild cards, which we can use with LIKE operator to specify the filter. The wild cards are: %,_,[] and -.