SQL LIKE Guide

LIKE checks whether text matches a pattern, where % stands for any run of characters and _ for exactly one. It’s how you find values that contain, start with or end with some text. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.

How to use LIKE

LIKE compares a value with a pattern. The pattern has to match the whole value, with two wildcards to fill the gaps: % matches any number of characters (including none) and _ matches exactly one. NOT LIKE returns the opposite.

expression [NOT] LIKE pattern [ESCAPE escape_character]

-- In SQL Server, LIKE only works in a condition, such as a WHERE clause
SELECT 'Joe Bloggs' LIKE 'Jo%';
-- Output: true

SELECT 'Joe Bloggs' LIKE 'J_e%';
-- Output: true

SELECT 'Joe Bloggs' LIKE 'Bloggs';
-- Output: false

The last one is false because, without a wildcard, the pattern has to equal the whole value. In a query, LIKE usually goes in the WHERE clause:

-- Input: customers
-- id | name
-- ---+-------------
-- 1  | Joe Bloggs
-- 2  | Jane Doe
-- 3  | Joanna Smith
-- 4  | Bob Jones

SELECT name
FROM customers
WHERE name LIKE 'Jo%';

-- Output:
-- name
-- ------------
-- Joe Bloggs
-- Joanna Smith

Rules to know

• PostgreSQL’s LIKE is case-sensitive. The other databases ignore case by default, but in PostgreSQL 'Smith' LIKE 'smith' is false. Lower-case both sides, as in LOWER(name) LIKE '%smith%', when case shouldn’t matter.
• NULL never matches. NULL LIKE '%' is NULL, not true, so rows where the column is NULL are left out by both LIKE and NOT LIKE. Add OR column IS NULL if you want them.

SELECT NULL LIKE '%';
-- Output: NULL

• Escape % and _ to match them literally. Put an escape character in front of the wildcard and name it with ESCAPE:

-- Input: discounts
-- id | description
-- ---+---------------------
-- 1  | 50% off winter coats
-- 2  | Spend 50, save 10
-- 3  | Up to 25% off shoes

-- Without escaping, the % after 50 is a wildcard
SELECT * FROM discounts
WHERE description LIKE '%50%%';

-- Output:
-- id | description
-- ---+---------------------
-- 1  | 50% off winter coats
-- 2  | Spend 50, save 10

-- With ESCAPE '\', the \% matches a real percent sign
-- (in MySQL, write ESCAPE '\\': a single backslash escapes the quote)
SELECT * FROM discounts
WHERE description LIKE '%50\%%' ESCAPE '\';

-- Output:
-- id | description
-- ---+---------------------
-- 1  | 50% off winter coats

• A leading % can’t use an index. A prefix search like LIKE 'INV-2026-%' can use an index on the column, but LIKE '%widget%' has to check every row. To search for words in large amounts of text, use your database’s full-text search instead.

Alternative syntax

You’ll also meet these database-specific extras:

-- PostgreSQL: ILIKE ignores case
SELECT 'SMITH' ILIKE 'smith';
-- Output: true

-- SQL Server: [ ] in a LIKE pattern matches one character from a set or range
SELECT CASE WHEN 'B12' LIKE '[A-C]%' THEN 'match' ELSE 'no match' END;
-- Output: match

-- MySQL: REGEXP for regular expressions (PostgreSQL uses ~)
SELECT 'A12-345' REGEXP '^A[0-9]{2}-[0-9]{3}$';
-- Output: 1

To check whether text contains a fixed string, without a pattern, find its position. The result is 0 when the string isn’t there:

SELECT INSTR('FinTech Corp', 'Tech');
-- Output: 4

-- PostgreSQL: POSITION('Tech' IN 'FinTech Corp')
-- SQL Server: CHARINDEX('Tech', 'FinTech Corp')

SQL Server’s CONTAINS() isn’t a substring test: it’s a full-text search predicate that needs a full-text index.

When should you use LIKE?

Use LIKE when you know part of a value but not all of it. Most uses fall into one of these four.

Find text that contains a word

Put % on both sides of the word. '%Tech%' matches “Tech Solutions Inc”, “FinTech Corp” and “Technology Partners”, because it matches inside longer words too.

Match the start or end of a value

Put % only at the end to match a prefix, like 'INV-2026-%', or only at the start to match a suffix, like '%.pdf' or '%@gmail.com'. Prefix searches are the ones an index can speed up.

Match a fixed format

_ stands for exactly one character, so a pattern can describe a value’s shape: '___-___-____' for a phone number, 'A__-___' for a product code. With NOT LIKE, the same pattern finds the values that break the format.

Search without worrying about case

Users type “smith”; the data says “Smith”. Lower-case both sides with LOWER() and the search ignores case, even in PostgreSQL.

Real examples of LIKE

Search for any of several words

Combine patterns with OR to find products that match any of them:

-- Input: products
-- id | name
-- ---+-----------------
-- 1  | Gaming laptop
-- 2  | Spiral notebook
-- 3  | Desk lamp
-- 4  | Desktop computer

SELECT * FROM products
WHERE name LIKE '%laptop%'
   OR name LIKE '%notebook%'
   OR name LIKE '%computer%';

-- Output:
-- id | name
-- ---+-----------------
-- 1  | Gaming laptop
-- 2  | Spiral notebook
-- 4  | Desktop computer

Find this year’s invoices

Invoice numbers start with a type and a year, so a prefix pattern picks out 2026’s invoices and skips credit notes:

-- Input: invoices
-- invoice_no    | customer
-- --------------+----------
-- INV-2026-0012 | Northwind
-- INV-2025-0981 | Contoso
-- CN-2026-0003  | Northwind
-- INV-2026-0040 | Fabrikam

SELECT * FROM invoices
WHERE invoice_no LIKE 'INV-2026-%';

-- Output:
-- invoice_no    | customer
-- --------------+----------
-- INV-2026-0012 | Northwind
-- INV-2026-0040 | Fabrikam

Find phone numbers in the wrong format

One _ per character describes the format you expect, and NOT LIKE lists the rows to clean up:

-- Input: customers
-- name      | phone
-- ----------+---------------
-- Ana Lima  | 555-010-1234
-- Ben Ode   | (555) 010-9876
-- Cara Diaz | 5550104321
-- Dev Rao   | 555-010-5555

SELECT name, phone FROM customers
WHERE phone NOT LIKE '___-___-____';

-- Output:
-- name      | phone
-- ----------+---------------
-- Ben Ode   | (555) 010-9876
-- Cara Diaz | 5550104321

Find a surname in any case

Names typed in capitals or lower case still turn up when both sides are lower-cased:

-- Input: customers
-- id | name
-- ---+------------
-- 1  | Anna Smith
-- 2  | BEN SMITH
-- 3  | Dana Jones
-- 4  | carla smith

SELECT * FROM customers
WHERE LOWER(name) LIKE '%smith%';

-- Output:
-- id | name
-- ---+------------
-- 1  | Anna Smith
-- 2  | BEN SMITH
-- 4  | carla smith

Further reading about LIKE

On this blog:

• SQL SUBSTRING guide: extract part of a string once you’ve found it
• SQL not equal guide: NOT LIKE alongside the other ways to exclude rows
• SQL CASE statement guide: label rows with conditions, including LIKE patterns
• Creating indexes in PostgreSQL: the indexes that make prefix searches fast
• MySQL CREATE INDEX tutorial: the same for MySQL

Official documentation:

• PostgreSQL: LIKE
• MySQL: LIKE
• SQLite: The LIKE, GLOB, REGEXP, MATCH, and extract operators
• SQL Server: LIKE