What wildcards can be used with SQL LIKE?

Two wildcards are used with LIKE: % (percent) matches any sequence of zero or more characters, and _ (underscore) matches exactly one character. For example, 'J%' matches any value starting with J, and '_an' matches any 3-character value ending in 'an'.

Is SQL LIKE case-sensitive?

Case sensitivity depends on the database and its collation settings. MySQL with a case-insensitive collation treats LIKE as case-insensitive. PostgreSQL's LIKE is case-sensitive; use ILIKE for case-insensitive matching.

What is the difference between LIKE '%text%' and LIKE 'text%'?

'%text%' matches any string that contains 'text' anywhere within it. 'text%' only matches strings that start with 'text'. Similarly, '%text' matches strings that end with 'text'.

Can SQL LIKE be used with NOT?

Yes. NOT LIKE excludes rows that match a pattern. For example: WHERE Store_Name NOT LIKE '%San%' returns all rows where the store name does not contain the substring 'San'.

SQL LIKE Operator | Pattern Matching in SQL Queries

SQL > SQL Commands > Like

The LIKE operator is used to filter the result set based on a string pattern. It is always used in the WHERE clause.

The SQL LIKE operator searches for a pattern within a text column using wildcard characters: % matches any sequence of characters and _ matches exactly one character.

Syntax

The syntax for the LIKE operator is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN};

{PATTERN} often consists of wildcards. We saw several examples of wildcard matching in the previous section.

Example

We use the following table for our example.

Table Store_Information

Store_Name	Sales	Txn_Date
LOS ANGELES	1500	Jan-05-1999
SAN DIEGO	250	Jan-07-1999
SAN FRANCISCO	300	Jan-08-1999
BOSTON	700	Jan-08-1999

We want to find all stores whose name contains 'AN'. To do so, we key in,

SELECT *
FROM Store_Information
WHERE Store_Name LIKE '%AN%';

Result:

Store_Name	Sales	Txn_Date
LOS ANGELES	1500	Jan-05-1999
SAN DIEGO	250	Jan-07-1999
SAN FRANCISCO	300	Jan-08-1999

The "%" sign before 'AN' means that there may be 0, 1, or more characters before the pattern 'AN.' The "%" sign after 'AN' means that there may be 0, 1, or more characters after the pattern 'AN.' Out of the four store names, 'LOS ANGELES,' 'SAN DIEGO,' and 'SAN FRANCISCO' all contain this pattern.

Frequently Asked Questions

How do I search for a literal percent sign or underscore with LIKE?

Use an escape character. Most databases support the ESCAPE clause: e.g., WHERE note LIKE '50\%' ESCAPE '\' searches for the literal string "50%". Alternatively, some databases let you place the wildcard characters in brackets (SQL Server) like [%].

How does LIKE differ from using = in a WHERE clause?

The equality operator (=) requires an exact match of the entire value. LIKE allows partial or pattern-based matching with wildcards. Use = when you know the full value, and LIKE when you need to search for a substring or pattern.

Can I use LIKE with numbers or dates?

LIKE is designed for character string matching. While some databases will implicitly convert numbers or dates to strings for a LIKE comparison, it's best practice to use comparison operators (>=, <=, BETWEEN) for numeric and date ranges.

What is SQL ILIKE and how is it different from LIKE?

ILIKE is a PostgreSQL extension that performs case-insensitive pattern matching, equivalent to LIKE on a case-insensitive collation. In MySQL, you achieve the same effect by using a case-insensitive collation or the LOWER() function: WHERE LOWER(col) LIKE 'pattern'.

Exercises

For these exercises, assume we have a table called User_Sales with the following data:

Table User_Sales

First_Name	Last_Name	Birth_Date	Gender	Join_Date	Total_Sales
Sophie	Lee	Jan-05-1960	F	Apr-05-2015	500
Richard	Brown	Jan-07-1975	M	Apr-05-2015	200
Jamal	Santo	Oct-08-1983	M	Apr-09-2015	350
Casey	Healy	Sep-20-1969	M	Apr-09-2015	80
Jill	Wilkes	Nov-20-1979	F	Apr-15-2015	210

1) 1. Which of the following SQL statement is valid? (There can be more than one answer)
a) SELECT First_Name, Last_Name FROM User_Sales WHERE First_Name LIKE 'A%' Last_Name LIKE 'W%';
b) SELECT First_Name, Last_Name FROM User_Sales WHERE First_Name LIKE 'J%' AND Last_Name LIKE 'W%';
c) SELECT First_Name, Last_Name FROM User_Sales First_Name LIKE 'J%' AND Last_Name LIKE 'W%';
d) SELECT First_Name, Last_Name FROM User_Sales WHERE First_Name LIKE 'J%', Last_Name LIKE 'W%';

2. How many records will be returned by the following query? (Assuming the database is configured to be case-insensitive)
SELECT * FROM User_Sales WHERE Last_Name LIKE '%l_e%';

3. How many records will be returned by the following query? (Assuming the database is configured to be case-insensitive)
SELECT * FROM User_Sales WHERE First_Name LIKE '%a%' OR Last_Name LIKE '%e%';

Next: SQL ILIKE

This page was last updated on March 19, 2026.

SQL LIKE