SQL LIKE

What is the SQL LIKE operator?

LIKE performs pattern matching on character, varchar, and text columns using % and _ wildcards.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL LIKE: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, and most others

SQL LIKE Full Explanation

LIKE is a comparison operator that evaluates whether a column value matches a specified pattern. It is case-sensitive or case-insensitive depending on the database collation and dialect. Two wildcards are available: % represents any sequence of zero or more characters, while _ represents exactly one character. You can combine literal characters and wildcards to build flexible search conditions.By default, the backslash (\\) is treated as an escape character in some systems, but the SQL standard lets you explicitly define any single-character escape sequence with the ESCAPE clause. When ESCAPE is present, the chosen character forces the following character to be interpreted literally, allowing you to search for the wildcard symbols themselves.LIKE returns TRUE, FALSE, or UNKNOWN (NULL) in three-valued logic. NOT LIKE negates the result. Unlike regular expressions, LIKE patterns are simple and portable, making them useful for quick substring or prefix searches, though they may not use indexes efficiently when the pattern starts with a leading wildcard.

SQL LIKE Syntax

-- Basic pattern match
column_name LIKE pattern

-- With NOT
column_name NOT LIKE pattern

-- Explicit escape character
column_name LIKE pattern ESCAPE 'c'

SQL LIKE Parameters

pattern (string) - Quoted string containing % and/or _ wildcards.
escape (string, optional) - Single character following the ESCAPE keyword that defines the escape character for the pattern.

Example Queries Using SQL LIKE

-- Find names starting with 'A'
SELECT * FROM customers WHERE name LIKE 'A%';

-- Emails ending in gmail.com
SELECT email FROM users WHERE email LIKE '%@gmail.com';

-- Serial numbers with exactly two letters followed by three digits
SELECT serial_no FROM devices WHERE serial_no LIKE '__###';

-- Search for 50% literal using ESCAPE
SELECT comment FROM reviews
WHERE comment LIKE '%50!%%' ESCAPE '!';

Expected Output Using SQL LIKE

Each query filters rows where the column value matches the pattern, returning only those records
Non-matching rows are excluded

Use Cases with SQL LIKE

Prefix, suffix, or substring search when full-text indexing is unnecessary
Data quality checks for malformed values
Quick ad-hoc filtering in exploratory analysis
Implementing simple wild-card search in application forms

Common Mistakes with SQL LIKE

Forgetting that '%text%' prevents index use and can be slow on large tables
Assuming LIKE is always case-insensitive (varies by collation)
Misplacing wildcards (e.g., 'A_%' vs 'A%_')
Using more than one character in ESCAPE clause

First Introduced In

SQL-92

Frequently Asked Questions

What does the _ wildcard match?

It matches exactly one character. For example, 'A_' matches 'AB' and 'A1' but not 'ABC'.

How is NOT LIKE different from LIKE?

NOT LIKE returns TRUE when the value does not match the pattern, effectively negating the condition.

Can I combine LIKE with other operators?

Yes. You can use AND, OR, and parentheses to chain multiple LIKE conditions or mix them with other predicates.

When should I use REGEXP instead of LIKE?

Choose REGEXP when you need complex pattern matching such as character classes or quantifiers that LIKE cannot express.