SQL LIKE Operator

SQL LIKE Operator : SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards used in conjunction with the LIKE operator:

• % – The percent sign represents zero, one, or multiple characters
• _ – The underscore represents a single character

Note:

• MS Access uses a question mark (?) instead of the underscore (_).
• The percent sign and the underscore can also be used in combinations!

LIKE Syntax

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

Tip:

• You can also combine any number of conditions using AND or OR operators.
• Here are some examples showing different LIKE operators with ‘%’ and ‘_’ wildcards:

LIKE Operator	Description
WHERE CustomerName LIKE ‘a%’	Finds any values that starts with “a”
WHERE CustomerName LIKE ‘%a’	Finds any values that ends with “a”
WHERE CustomerName LIKE ‘%or%’	Finds any values that have “or” in any position
WHERE CustomerName LIKE ‘_r%’	Finds any values that have “r” in the second position
WHERE CustomerName LIKE ‘a_%_%’	Finds any values that starts with “a” and are at least 3 characters in length
WHERE ContactName LIKE ‘a%o’	Finds any values that starts with “a” and ends with “o”

 

Demo Database

Below is a selection from the “Customers” table in the Northwind sample database:

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

 

SQL LIKE Examples

The following SQL statement selects all customers with a CustomerName starting with “a”:

Example

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

output

The following SQL statement selects all customers with a CustomerName ending with “a”:

Number of Records: 4

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

Example

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

 

The following SQL statement selects all customers with a CustomerName that have “or” in any position:

Example

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

 

Output

Number of Records: 11

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
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
36	Hungry Coyote Import Store	Yoshi Latimer	City Center Plaza 516 Main St.	Elgin	97827	USA
40	La corne d’abondance	Daniel Tonini	67, avenue de l’Europe	Versailles	78000	France
43	Lazy K Kountry Store	John Steel	12 Orchestra Terrace	Walla Walla	99362	USA
52	Morgenstern Gesundkost	Alexander Feuer	Heerstr. 22	Leipzig	04179	Germany
53	North/South	Simon Crowther	South House 300 Queensbridge	London	SW7 1RZ	UK
55	Old World Delicatessen	Rene Phillips	2743 Bering St.	Anchorage	99508	USA
72	Seven Seas Imports	Hari Kumar	90 Wadhurst Rd.	London	OX15 4NB	UK
80	Tortuga Restaurante	Miguel Angel Paolino	Avda. Azteca 123	México D.F.	05033	Mexico
88	Wellington Importadora	Paula Parente	Rua do Mercado, 12	Resende	08737-363	Brazil

 

The following SQL statement selects all customers with a CustomerName that have “r” in the second position:

Example

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

 

Output

Number of Records: 11

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
17	Drachenblut Delikatessend	Sven Ottlieb	Walserweg 21	Aachen	52066	Germany
20	Ernst Handel	Roland Mendel	Kirchgasse 6	Graz	8010	Austria
25	Frankenversand	Peter Franken	Berliner Platz 43	München	80805	Germany
26	France restauration	Carine Schmitt	54, rue Royale	Nantes	44000	France
27	Franchi S.p.A.	Paolo Accorti	Via Monte Bianco 34	Torino	10100	Italy
32	Great Lakes Food Market	Howard Snyder	2732 Baker Blvd.	Eugene	97403	USA
33	GROSELLA-Restaurante	Manuel Pereira	5ª Ave. Los Palos Grandes	Caracas	1081	Venezuela
60	Princesa Isabel Vinhoss	Isabel de Castro	Estrada da saúde n. 58	Lisboa	1756	Portugal
81	Tradição Hipermercados	Anabela Domingues	Av. Inês de Castro, 414	São Paulo	05634-030	Brazil
82	Trail’s Head Gourmet Provisioners	Helvetius Nagy	722 DaVinci Blvd.	Kirkland	98034	USA

 

The following SQL statement selects all customers with a CustomerName that starts with “a” and are at least 3 characters in length:

Example

SELECT * FROM Customers
WHERE CustomerName LIKE ‘a_%_%’;

Output

Number of Records: 4

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

 

The following SQL statement selects all customers with a ContactName that starts with “a” and ends with “o”:

Example

SELECT * FROM Customers
WHERE ContactName LIKE ‘a%o’;

Output

Number of Records: 3

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
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
69	Romero y tomillo	Alejandra Camino	Gran Vía, 1	Madrid	28001	Spain

 

The following SQL statement selects all customers with a CustomerName that does NOT start with “a”:

Example

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

 

output

Number of Records: 12

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden
6	Blauer See Delikatessen	Hanna Moos	Forsterstr. 57	Mannheim	68306	Germany
7	Blondel père et fils	Frédérique Citeaux	24, place Kléber	Strasbourg	67000	France
8	Bólido Comidas preparadas	Martín Sommer	C/ Araquil, 67	Madrid	28023	Spain
9	Bon app’	Laurence Lebihans	12, rue des Bouchers	Marseille	13008	France
10	Bottom-Dollar Marketse	Elizabeth Lincoln	23 Tsawassen Blvd.	Tsawassen	T2F 8M4	Canada
11	B’s Beverages	Victoria Ashworth	Fauntleroy Circus	London	EC2 5NT	UK
12	Cactus Comidas para llevar	Patricio Simpson	Cerrito 333	Buenos Aires	1010	Argentina
13	Centro comercial Moctezuma	Francisco Chang	Sierras de Granada 9993	México D.F.	05022	Mexico
14	Chop-suey Chinese	Yang Wang	Hauptstr. 29	Bern	3012	Switzerland
15	Comércio Mineiro	Pedro Afonso	Av. dos Lusíadas, 23	São Paulo	05432-043	Brazil
16	Consolidated Holdings	Elizabeth Brown	Berkeley Gardens 12 Brewery	London	WX1 6LT	UK