SQL LIKE

= matches an exact value. LIKE matches a pattern, useful for "starts with," "contains," or "ends with" searches without knowing the exact text in advance. The full wildcard syntax (% and _) gets its own lesson next, this one focuses on LIKE itself.

Learning Objectives

Use LIKE to match text against a pattern.
Use NOT LIKE to exclude matching text.
Know which databases treat LIKE as case-sensitive by default, and which don't.

Pattern

'H%'

Compared against each row's text

Matches

Hopper, Hamilton...

Basic LIKE

% matches any number of characters, including none. 'H%' matches any text starting with H. The LIKE keyword itself, and this basic usage, is identical across every database in this course.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'H%';

-- Result:
-- first_name | last_name
-- Grace      | Hopper
-- Margaret   | Hamilton

NOT LIKE

NOT LIKE excludes matching rows instead of including them, every employee whose last name doesn't start with H.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

SELECT first_name, last_name
FROM employees
WHERE last_name NOT LIKE 'H%';

Case Sensitivity Differs by Database

Database	LIKE case-sensitive by default?
MySQL / MariaDB	No (depends on the column's collation, but usually no)
SQLite	No, for standard ASCII text
SQL Server	No (depends on the database's collation, but usually no)
PostgreSQL	Yes, has a separate ILIKE for case-insensitive matching
Oracle	Yes, no built-in case-insensitive LIKE variant

PostgreSQL case-insensitive variant

SELECT first_name, last_name
FROM employees
WHERE last_name ILIKE 'h%';

Oracle case-insensitive workaround

SELECT first_name, last_name
FROM employees
WHERE UPPER(last_name) LIKE UPPER('h%');

NOTE

This is the one genuine difference worth remembering: a LIKE pattern that matches on MySQL or SQL Server can silently stop matching mixed-case data on PostgreSQL or Oracle. Always confirm case sensitivity against the specific database you're actually using.

Try It Yourself

Write a query that finds every employee whose first_name ends in "a", for a database with case-sensitive LIKE (like Oracle or PostgreSQL), accounting for uppercase or lowercase A.

Answer

SELECT first_name
FROM employees
WHERE UPPER(first_name) LIKE '%A';

Frequently Asked Questions

Is LIKE slower than =?

A pattern starting with a fixed prefix, like 'H%', can often still use a standard index efficiently on most databases. A pattern starting with %, like '%son', generally can't, and requires scanning every row, slower on a large table.

Can LIKE match numbers?

LIKE works on text (string) data. Matching a numeric column usually requires converting it to text first, the exact function for that varies by database and is covered later in this course.

Common Beginner Mistakes

Assuming LIKE is case-insensitive on every database

It's case-insensitive by default on MySQL, MariaDB, SQLite, and (usually) SQL Server, but case-sensitive on PostgreSQL and Oracle. A pattern that works while testing on one database can silently stop matching rows on another.

Using LIKE for an exact match with no wildcards at all

WHERE department LIKE 'Engineering' works, but it's just a slower, more roundabout way to write WHERE department = 'Engineering'. Use LIKE only when a pattern with % or _ is actually involved.

Best Practices

Confirm case sensitivity on your specific database before relying on LIKE matching mixed-case data.
Use = instead of LIKE whenever there's no actual wildcard in the pattern.
On PostgreSQL, reach for ILIKE directly instead of wrapping both sides in UPPER()/LOWER(), it's clearer and can use certain indexes UPPER() can't.

Interview Questions

Why might a LIKE query that works correctly in development fail to match rows in a different environment?

If development uses MySQL (case-insensitive LIKE by default) and production uses PostgreSQL or Oracle (case-sensitive), a pattern like 'hopper%' matching 'Hopper' in development will stop matching in production. Case sensitivity is a database-level (or collation-level) setting, not a property of LIKE itself.

Write a case-insensitive LIKE query for PostgreSQL, and the equivalent for Oracle.

PostgreSQL: WHERE last_name ILIKE 'h%'; Oracle: WHERE UPPER(last_name) LIKE UPPER('h%'); — PostgreSQL has a dedicated case-insensitive operator, Oracle requires normalizing both sides to the same case manually.

Summary

This lesson covered the basics of pattern matching with LIKE and NOT LIKE, and the one real cross-database difference in this topic: case sensitivity, which varies between MySQL/MariaDB/SQLite/SQL Server (case-insensitive by default) and PostgreSQL/Oracle (case-sensitive).

What's Next?

The next lesson covers wildcards in full depth, % and _, combining them, and escaping a literal wildcard character.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.