SQL LIKE

SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;

Demo Database

Here is a snippet from th Customers table utilized in the examples:

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

The _ Wildcard

The _ wildcard denotes a single character,

It can be any character or number. However, each _ represents precisely one character.

Example

Retrieve all customers from a city that begins with ‘L’, followed by one wildcard character, ‘nd’, and then two wildcard characters.

SELECT * FROM Customers WHERE city LIKE ‘L_nd__’;

The % Wildcard

The % wildcard signifies any number of characters, including zero characters.

Example

Retrieve all customers from cities containing the letter ‘L’.

SELECT * FROM Customers WHERE city LIKE ‘%L%’;

Starts With

To retrieve records that begin with a specific letter or phrase, append % at the end of the letter or phrase.

Example

Retrieve all customers whose names start with ‘La’.

SELECT * FROM Customers WHERE CustomerName LIKE ‘La%’;

 

Tip: You can also use AND or OR operators to combine multiple conditions.

Example

Retrieve all customers whose names start with ‘a’ or ‘b’.

SELECT * FROM Customers WHERE CustomerName LIKE ‘a%’ OR CustomerName LIKE ‘b%’;

Ends With

To retrieve records that end with a specific letter or phrase, prepend % at the beginning of the letter or phrase.

Example

Retrieve all customers whose names end with ‘a’.

SELECT * FROM Customers WHERE CustomerName LIKE ‘%a’;

 

Tip: You can also combine “starts with” and “ends with”:

Example

Retrieve all customers whose names start with “b” and end with “s”.

SELECT * FROM Customers WHERE CustomerName LIKE ‘b%s’;

Contains

To retrieve records containing a specific letter or phrase, place % both before and after the letter or phrase.

Example

Retrieve all customers whose names contain the phrase ‘or’.

SELECT * FROM Customers WHERE CustomerName LIKE ‘%or%’;

Combine Wildcards

Wildcards such as % and _ can be combined with each other.

Example

Retrieve all customers whose names start with “a” and are at least 3 characters long.

SELECT * FROM Customers WHERE CustomerName LIKE ‘a__%’;

Example

Retrieve all customers where “r” appears in the second position of their names.

SELECT * FROM Customers WHERE CustomerName LIKE ‘_r%’;

Without Wildcard

If no wildcard is specified, the phrase must exactly match to yield a result.

Example

Retrieve all customers who are from Spain.

SELECT * FROM Customers WHERE Country LIKE ‘Spain’;