SQL Like

The LIKE operator is used in a WHERE clause to search for a specific text pattern within a column. It allows you to perform partial matches when you don't know the exact string value you are looking for.

To build these search patterns, SQL uses two primary wildcards:

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

Common Wildcard Patterns

Syntax Rule	What it Searches For	Real-World Example
'A%'	Starts with "A"	WHERE customer_name LIKE 'A%' (Finds Alice, Alex)
'%a'	Ends with "a"	WHERE customer_name LIKE '%a' (Finds Amanda, Lisa)
'%or%'	Contains "or" in any position	WHERE job_title LIKE '%or%' (Finds Director, Coordinator)
'_r%'	Has "r" in the second position	WHERE first_name LIKE '_r%' (Finds Brandon, Eric)
'A____%'	Starts with "A" and is at least 5 characters long	WHERE username LIKE 'A____%'

Syntax Examples

Find Emails from a Specific Domain

-- Extracts any customer using a Gmail address
SELECT first_name, email FROM customers
WHERE email LIKE '%@gmail.com';

Find Names Starting and Ending with Specific Letters

-- Finds names like "Robert" or "Rupert"
SELECT * FROM employees
WHERE first_name LIKE 'R%t';

Reversing the Logic (NOT LIKE)

You can combine the operator with NOT to exclude rows that match a specific text layout.

-- Finds all products whose serial number does NOT start with "TEST-"
SELECT product_id, product_name FROM products
WHERE product_code NOT LIKE 'TEST-%';

Important Rules to Keep in Mind

Case Sensitivity: In some databases like MySQL and SQL Server, LIKE is case-insensitive by default ('a%' finds "Alex"). In other databases like PostgreSQL, LIKE is case-sensitive. To run a case-insensitive search in PostgreSQL, use the ILIKE keyword instead.
Performance Warning: Queries using LIKE '%pattern%' (with a wildcard at the beginning) cannot use standard database indexes efficiently. This forces the database engine to run a full table scan, which can slow down execution on large datasets.