SQL - LIKE Operator

SQL - Pattern Matching with the LIKE Operator

Introduction to Pattern Matching

How do you find a customer when you only remember part of their name? Or find all products with a specific model number prefix? For these scenarios, simple equality checks (=) are not enough. This is where the SQL LIKE operator comes in. It’s used in a WHERE clause to search for a specified pattern in a column.

The LIKE Operator and its Wildcards (%, _)

The LIKE operator’s power comes from two special wildcard characters:

• The Percent Sign (%): Represents zero, one, or multiple characters.
• The Underscore (_): Represents a single character.

Syntax

SELECT column_list
FROM table_name
WHERE column_name LIKE pattern;

Setup: Sample CUSTOMERS Table

For our examples, let’s use the following CUSTOMERS table.

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    CITY VARCHAR(50)
);

INSERT INTO CUSTOMERS (ID, NAME, CITY) VALUES
    (1, 'Ramesh Kumar', 'Ahmedabad'),
    (2, 'Khilan Sharma', 'Delhi'),
    (3, 'Kaushik Patel', 'Kota'),
    (4, 'Chaitali Singh', 'Mumbai'),
    (5, 'Hardik Pandya', 'Bhopal'),
    (6, 'Komal Verma', 'Hyderabad'),
    (7, 'Muffy', 'Indore');

Practical LIKE Patterns

Here are the most common ways to use LIKE:

Pattern	Description	Example Query	Result
'K%'	Finds any values that start with ‘K’.	WHERE NAME LIKE ‘K%'	'Khilan Sharma’, ‘Kaushik Patel’, ‘Komal Verma’
'%a'	Finds any values that end with ‘a’.	WHERE NAME LIKE ‘%a'	'Komal Verma’
'%al%'	Finds any values that have ‘al’ in any position.	WHERE NAME LIKE ‘%al%'	'Khilan Sharma’, ‘Chaitali Singh’, ‘Komal Verma’
'_h%'	Finds any values that have ‘h’ in the second position.	WHERE NAME LIKE ‘_h%'	'Khilan Sharma’, ‘Chaitali Singh’
'K__%'	Finds any values that start with ‘K’ and are at least 3 characters long.	WHERE NAME LIKE ‘K__%'	'Khilan Sharma’, ‘Kaushik Patel’, ‘Komal Verma’
'M%y'	Finds any values that start with ‘M’ and end with ‘y’.	WHERE NAME LIKE ‘M%y'	'Muffy’

Advanced Usage: NOT, OR, and ESCAPE

Using NOT LIKE

To find rows that do not match a pattern, simply use the NOT operator.

-- Find all customers whose name does NOT start with 'K'
SELECT NAME FROM CUSTOMERS WHERE NAME NOT LIKE 'K%';

Searching for a Literal Wildcard: The ESCAPE Clause

What if you need to find a value that literally contains a ’%’ or ’_’ character? For example, searching for products with a ‘50%’ discount in their name. You use the ESCAPE clause to define an ‘escape character’ that tells SQL to treat the following wildcard as a literal character.

CREATE TABLE Deals (ProductName VARCHAR(100));
INSERT INTO Deals VALUES ('Summer Sale - 50% Off!'), ('Standard Item');

-- Find products with a literal '%' in the name. Here, '!' is the escape character.
SELECT ProductName FROM Deals
WHERE ProductName LIKE '%!%%' ESCAPE '!';

-- Result: 'Summer Sale - 50% Off!'

Important Considerations: Performance and Security

Performance: The Leading Wildcard Problem

A LIKE query’s performance heavily depends on the pattern. A query like WHERE name LIKE 'K%' is fast because the database can use an index on the name column. However, a query with a leading wildcard, such as WHERE name LIKE '%mer', is often very slow. It cannot use a standard index and must perform a full table scan, reading every single row to check for a match. Avoid leading wildcards on large tables whenever possible.

Security: Preventing SQL Injection

Never concatenate user input directly into a LIKE query string. This creates a serious security vulnerability called SQL Injection. Always use parameterized queries or prepared statements, which safely handle user input.

-- BAD: Vulnerable to SQL Injection
-- query = "SELECT * FROM USERS WHERE name LIKE '%" + userInput + "%'";

-- GOOD: Using a parameterized query (syntax varies by language)
-- query = "SELECT * FROM USERS WHERE name LIKE ?";
-- parameters = ["%" + userInput + "%"];

Beyond LIKE: When to Use Other Tools

LIKE is great for simple patterns, but for more complex needs, consider these alternatives:

• Regular Expressions (REGEXP, RLIKE): For complex pattern matching (e.g., finding valid email formats or specific number sequences), regular expressions offer far more power and flexibility than LIKE. This is a non-standard feature, but available in many RDBMS like MySQL and PostgreSQL.
• Full-Text Search (FTS): When you need to search for words or phrases within large blocks of text (like articles or product descriptions), FTS is the right tool. It’s much faster and more capable than LIKE '%word%', offering features like stemming (matching ‘run’, ‘ran’, ‘running’) and relevance ranking.