SQL LIKE Operator

The LIKE operator in SQL is used to search for patterns within a string column. It's much more flexible than a simple equality comparison because it allows you to use wildcard characters to match various string patterns.

LIKE Operator: Definition and Usage

The LIKE operator is used within a WHERE clause to filter rows based on partial string matches. It employs wildcard characters to specify patterns rather than exact matches.

Wildcard Characters

% (percent sign): Matches any sequence of zero or more characters.
_ (underscore): Matches any single character.

These wildcards make pattern matching very flexible.

Syntax


SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'pattern';

Example Database

The following examples use this sample 'Customers' table:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden
6	Gusto Grezzo	Giovanni Rovelli	Via Degli Ortolani, 10	Reggio Emilia	42100	Italy

Examples Using LIKE

Starts With "a"

Finds customers whose names start with "a".

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE 'a%';

Output


(Customers whose names begin with 'a' will be returned.)

Ends With "a"

Finds customers whose names end with "a".

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE '%a';

Output


(Customers whose names end with 'a' will be returned.)

Contains "or"

Finds customers whose names contain "or".

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE '%or%';

Output


(Customers whose names contain 'or' will be returned.)

Starts with "L", then one character, then "nd", then any characters

Using the underscore wildcard(_).

Syntax


SELECT * FROM Customers WHERE city LIKE 'L_nd__';

Output


(Customers from cities matching the pattern 'L_nd__' will be returned.)

Starts with "a" and is at least 3 characters long

Combining wildcards.

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE 'a__%';

Output


(Customers whose names start with 'a' and have at least 3 characters will be returned.)

Contains "r" in the second position

Using the underscore wildcard to specify character position.

Syntax


SELECT * FROM Customers WHERE CustomerName LIKE '_r%';

Output


(Customers whose names have 'r' in the second position will be returned.)

Exact Match (No Wildcards)

Finding customers from Spain.

Syntax


SELECT * FROM Customers WHERE Country LIKE 'Spain';

Output


(Customers from Spain will be returned.)