LIKE Operator in SQL

DevSniper
Published : 18 July 2024

158 Views

In SQL, LIKE is an operator. LIKE operator is used in SQL to match a specified pattern within a column containing text data. LIKE supports the use of wildcard characters ( % and _) for pattern matching. Understanding the LIKE operator enhances the ability to perform complex searches, retrieve targeted data, and extract valuable insights from datasets. This article provides a detailed overview of the LIKE operator with example. After reading this article you can use the LIKE operator easily and retrieve valuable data from a table.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

When using the LIKE operator in SQL, two commonly used wildcards are

% (percent sign) represents zero, one, or multiple characters.
_ (underscore sign) represents exactly one single character.

Demo Database

CREATE TABLE `Employees` (
    `Id` INT PRIMARY KEY AUTO_INCREMENT,
    `Name` VARCHAR(50) NOT NULL,
    `Mobile` VARCHAR(20) NOT NULL,
    `JobTitle` VARCHAR(100) NOT NULL,
    `Department` VARCHAR(100) NOT NULL,
    `salary` DECIMAL(10, 2)
);

INSERT INTO Employees (Name, Mobile, JobTitle, Department, salary)
VALUES
    ('Ram', '9999999998', 'Software Engineer III', 'Engineering', 590000.00),
    ('Gopal', '9999999958', 'Marketing Manager', 'Marketing', 10000.00),
    ('Deepak', '9999999238', 'Software Engineer II', 'Engineering', 130000.00),
    ('Jone', '9999999538', 'Software Engineer', 'Engineering', 30000.00),
    ('Roopa', '9999994238', 'Software Engineer II', 'Engineering', 120000.00),
    ('Jack', '9999999128', 'Sr. Marketing Manager', 'Marketing', 130000.00),
    ('Deepak Sharma', '9999945238', 'Software Engineer II', 'Engineering', 230000.00),
    ('Reena', '9999999698', 'Data Analyst', 'Analytics', 80000.00),
    ('Rina', '9999999898', 'Data Analyst', 'Analytics', 90000.00),
    ('Jhon', '9999999978', 'HR Specialist', 'Human Resources', 75000.00);

LIKE with %

if you need to match zero or more than zero characters you can use % ( percent sign) wildcard in search pattern. The % wildcard is used to match any sequence of characters (including zero characters) in a search string. This flexibility enables comprehensive searches based on specified criteria.

SQL query to retrieve employees whose name start with `de`.

SELECT  Id,Name
FROM Employees
WHERE Name LIKE "de%";

Id        Name
-----------------------
3	Deepak
7	Deepak Sharma

SQL query to retrieve employees whose name end with `k`.

SELECT  Id,  Name
FROM Employees
WHERE Name LIKE "%k";

Id        Name
---------------------
3	Deepak
6	Jack

SQL query to retrieve employees whose name contains `pa`.

SELECT  Id, Name
FROM Employees
WHERE Name LIKE "%pa%";

Id	 Name
--------------------
3	Deepak
5	Gopal
7	Deepak Sharma

SQL query to retrieve employees whose name start with `r` and end with `a`.

SELECT  Id, Name
FROM Employees
WHERE Name LIKE "r%a";

Id	Name
-----------------
5	Roopa
8	Reena
9	Rina

LIKE with _ (underscore sign)

if you need to match exact one character you should use _ ( underscore sign) wildcard in search pattern. The _ (underscore sign) wildcard is used to match exact one character in a search string. This flexibility enables comprehensive searches based on specified criteria.

SQL query to retrieve employees whose names have exactly 4 characters.

SELECT  Id, Name
FROM Employees
WHERE Name LIKE "____";  // continues 4 _
        OR
SELECT  Id, Name
FROM Employees
WHERE LENGTH(Name) = 4;

Id	Name
----------------
4	Jone
6	Jack
9	Rina
10	Jhon

SQL query to retrieve employees whose names have exactly 4 characters and start with `J`.

SELECT  Id, Name
FROM Employees
WHERE Name LIKE "J___";

Id	Name
---------------
4	Jone
6	Jack
10	Jhon

Combine % and _ with LIKE

In SQL, wildcards such as % and _ can be used in combination with each other to create flexible and powerful search patterns.

SQL query to retrieve employees whose names start with `r` and have at least 4 characters.

SELECT  Id, Name
FROM Employees
WHERE Name LIKE "r___%";

Id	Name
-----------------
5	Roopa
8	Reena
9	Rina

LIKE Without Wildcard

In SQL, while the LIKE operator is typically used with wildcard characters (% and _) for pattern matching in searches. It can also be used effectively without any wildcard to match exact strings.

SQL query to retrieve employees whose name is `ram`.

SELECT  Id, Name
FROM Employees
WHERE Name LIKE "ram";

Id	Name
----------------
1	Ram

Indexing With LIKE

In SQL, Creating indexes on columns used with the LIKE operator can enhance query performance but it's crucial to understand how indexes work with LIKE queries to optimize database performance effectively.

Prefix Search Optimization

Indexes are most effective when the LIKE pattern has a constant prefix followed by a wildcard (prefix%). This configuration allows the database engine to efficiently utilize the index for quick lookup and retrieval of matching rows.

Example :- An index on column_name can efficiently support queries like WHERE column_name LIKE 'prefix%'

Leading Wildcards

Leading wildcards (%suffix) generally prevent efficient index usage because they force the database to scan the entire index or table to find matching rows. This can lead to slower query performance, especially on large datasets.

Different database management systems may vary in how they handle and optimize LIKE queries with indexes. refer to the documentation provided by your database vendor. Understanding these can help optimize query performance effectively based on your database's capabilities and configurations. Test and analyze query performance to ensure indexes are effectively supporting your LIKE queries. Use database tools and query execution plans to monitor index usage.

Key Characteristics of LIKE Operator

LIKE operator is used in SQL to match a specified pattern within a column containing text data.
LIKE supports the use of wildcard characters ( % and _) for pattern matching.
LIKE Can be used without wildcards to find exact matches of strings or patterns.
LIKE operator enhances the ability to perform complex searches, retrieve targeted data, and extract valuable insights from datasets
LIKE operator can efficiently filter large datasets, especially when combined with indexes on the columns being searched.
Overuse of leading wildcards (%text) can lead to slower query performance as it prevents efficient use of indexes.
Test and analyze query performance to ensure indexes are effectively supporting your LIKE queries. Use database tools and query execution plans to monitor index usage.