Tutorials References Exercises Videos Menu
Create Website Get Certified Upgrade
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP BOOTSTRAP HOW TO W3.CSS C C++ C# REACT R JQUERY DJANGO TYPESCRIPT NODEJS MYSQL

SQL Tutorial

SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL Union SQL Group By SQL Having SQL Exists SQL Any, All SQL Select Into SQL Insert Into Select SQL Case SQL Null Functions SQL Stored Procedures SQL Comments SQL Operators

SQL Database

SQL Create DB SQL Drop DB SQL Backup DB SQL Create Table SQL Drop Table SQL Alter Table SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Foreign Key SQL Check SQL Default SQL Index SQL Auto Increment SQL Dates SQL Views SQL Injection SQL Hosting SQL Data Types

SQL References

SQL Keywords
ADD ADD CONSTRAINT ALL ALTER ALTER COLUMN ALTER TABLE AND ANY AS ASC BACKUP DATABASE BETWEEN CASE CHECK COLUMN CONSTRAINT CREATE CREATE DATABASE CREATE INDEX CREATE OR REPLACE VIEW CREATE TABLE CREATE PROCEDURE CREATE UNIQUE INDEX CREATE VIEW DATABASE DEFAULT DELETE DESC DISTINCT DROP DROP COLUMN DROP CONSTRAINT DROP DATABASE DROP DEFAULT DROP INDEX DROP TABLE DROP VIEW EXEC EXISTS FOREIGN KEY FROM FULL OUTER JOIN GROUP BY HAVING IN INDEX INNER JOIN INSERT INTO INSERT INTO SELECT IS NULL IS NOT NULL JOIN LEFT JOIN LIKE LIMIT NOT NOT NULL OR ORDER BY OUTER JOIN PRIMARY KEY PROCEDURE RIGHT JOIN ROWNUM SELECT SELECT DISTINCT SELECT INTO SELECT TOP SET TABLE TOP TRUNCATE TABLE UNION UNION ALL UNIQUE UPDATE VALUES VIEW WHERE
MySQL Functions
String Functions: ASCII CHAR_LENGTH CHARACTER_LENGTH CONCAT CONCAT_WS FIELD FIND_IN_SET FORMAT INSERT INSTR LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM MID POSITION REPEAT REPLACE REVERSE RIGHT RPAD RTRIM SPACE STRCMP SUBSTR SUBSTRING SUBSTRING_INDEX TRIM UCASE UPPER Numeric Functions: ABS ACOS ASIN ATAN ATAN2 AVG CEIL CEILING COS COT COUNT DEGREES DIV EXP FLOOR GREATEST LEAST LN LOG LOG10 LOG2 MAX MIN MOD PI POW POWER RADIANS RAND ROUND SIGN SIN SQRT SUM TAN TRUNCATE Date Functions: ADDDATE ADDTIME CURDATE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURTIME DATE DATEDIFF DATE_ADD DATE_FORMAT DATE_SUB DAY DAYNAME DAYOFMONTH DAYOFWEEK DAYOFYEAR EXTRACT FROM_DAYS HOUR LAST_DAY LOCALTIME LOCALTIMESTAMP MAKEDATE MAKETIME MICROSECOND MINUTE MONTH MONTHNAME NOW PERIOD_ADD PERIOD_DIFF QUARTER SECOND SEC_TO_TIME STR_TO_DATE SUBDATE SUBTIME SYSDATE TIME TIME_FORMAT TIME_TO_SEC TIMEDIFF TIMESTAMP TO_DAYS WEEK WEEKDAY WEEKOFYEAR YEAR YEARWEEK Advanced Functions: BIN BINARY CASE CAST COALESCE CONNECTION_ID CONV CONVERT CURRENT_USER DATABASE IF IFNULL ISNULL LAST_INSERT_ID NULLIF SESSION_USER SYSTEM_USER USER VERSION
SQL Server Functions
String Functions: ASCII CHAR CHARINDEX CONCAT Concat with + CONCAT_WS DATALENGTH DIFFERENCE FORMAT LEFT LEN LOWER LTRIM NCHAR PATINDEX QUOTENAME REPLACE REPLICATE REVERSE RIGHT RTRIM SOUNDEX SPACE STR STUFF SUBSTRING TRANSLATE TRIM UNICODE UPPER Numeric Functions: ABS ACOS ASIN ATAN ATN2 AVG CEILING COUNT COS COT DEGREES EXP FLOOR LOG LOG10 MAX MIN PI POWER RADIANS RAND ROUND SIGN SIN SQRT SQUARE SUM TAN Date Functions: CURRENT_TIMESTAMP DATEADD DATEDIFF DATEFROMPARTS DATENAME DATEPART DAY GETDATE GETUTCDATE ISDATE MONTH SYSDATETIME YEAR Advanced Functions CAST COALESCE CONVERT CURRENT_USER IIF ISNULL ISNUMERIC NULLIF SESSION_USER SESSIONPROPERTY SYSTEM_USER USER_NAME
MS Access Functions
String Functions: Asc Chr Concat with & CurDir Format InStr InstrRev LCase Left Len LTrim Mid Replace Right RTrim Space Split Str StrComp StrConv StrReverse Trim UCase Numeric Functions: Abs Atn Avg Cos Count Exp Fix Format Int Max Min Randomize Rnd Round Sgn Sqr Sum Val Date Functions: Date DateAdd DateDiff DatePart DateSerial DateValue Day Format Hour Minute Month MonthName Now Second Time TimeSerial TimeValue Weekday WeekdayName Year Other Functions: CurrentUser Environ IsDate IsNull IsNumeric
SQL Quick Ref

SQL Examples

SQL Examples SQL Quiz SQL Exercises SQL Certificate

SQL Wildcards

❮ Previous Next ❯

SQL Wildcard Characters

A wildcard character is used to substitute one or more characters in a string.

Wildcard characters are used with the LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

Wildcard Characters in MS Access

Symbol Description Example
* Represents zero or more characters bl* finds bl, black, blue, and blob
? Represents a single character h?t finds hot, hat, and hit
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit
! Represents any character not in the brackets h[!oa]t finds hit, but not hot and hat
- Represents any single character within the specified range c[a-b]t finds cat and cbt
# Represents any single numeric character 2#5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295

Wildcard Characters in SQL Server

Symbol Description Example
% Represents zero or more characters bl% finds bl, black, blue, and blob
_ Represents a single character h_t finds hot, hat, and hit
[] Represents any single character within the brackets h[oa]t finds hot and hat, but not hit
^ Represents any character not in the brackets h[^oa]t finds hit, but not hot and hat
- Represents any single character within the specified range c[a-b]t finds cat and cbt

All the wildcards can also be used in combinations!

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

The table below shows the complete "Customers" table from the Northwind sample database:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.05021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.05023Mexico
4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden
6Blauer See DelikatessenHanna MoosForsterstr. 57Mannheim68306Germany
7Blondel père et filsFrédérique Citeaux24, place KléberStrasbourg67000France
8Bólido Comidas preparadasMartín SommerC/ Araquil, 67Madrid28023Spain
9Bon app'Laurence Lebihans12, rue des BouchersMarseille13008France
10Bottom-Dollar MarketseElizabeth Lincoln23 Tsawassen Blvd.TsawassenT2F 8M4Canada
11B's BeveragesVictoria AshworthFauntleroy CircusLondonEC2 5NTUK
12Cactus Comidas para llevarPatricio SimpsonCerrito 333Buenos Aires1010Argentina
13Centro comercial MoctezumaFrancisco ChangSierras de Granada 9993México D.F.05022Mexico
14Chop-suey ChineseYang WangHauptstr. 29Bern3012Switzerland
15Comércio MineiroPedro AfonsoAv. dos Lusíadas, 23São Paulo05432-043Brazil
16Consolidated HoldingsElizabeth BrownBerkeley Gardens 12 BreweryLondonWX1 6LTUK
17Drachenblut DelikatessendSven OttliebWalserweg 21Aachen52066Germany
18Du monde entierJanine Labrune67, rue des Cinquante OtagesNantes44000France
19Eastern ConnectionAnn Devon35 King GeorgeLondonWX3 6FWUK
20 Ernst Handel Roland Mendel Kirchgasse 6 Graz 8010 Austria
21 Familia Arquibaldo Aria Cruz Rua Orós, 92 São Paulo 05442-030 Brazil
22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain
23 Folies gourmandes Martine Rancé 184, chaussée de Tournai Lille 59000 France
24 Folk och fä HB Maria Larsson Åkergatan 24 Bräcke S-844 67 Sweden
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
28 Furia Bacalhau e Frutos do Mar Lino Rodriguez Jardim das rosas n. 32 Lisboa 1675 Portugal
29 Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23 Barcelona 08022 Spain
30 Godos Cocina Típica José Pedro Freyre C/ Romero, 33 Sevilla 41101 Spain
31 Gourmet Lanchonetes André Fonseca Av. Brasil, 442 Campinas 04876-786 Brazil
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
34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil
35 HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal 5022 Venezuela
36 Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin 97827 USA
37 Hungry Owl All-Night Grocers Patricia McKenna 8 Johnstown Road Cork Ireland
38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK
39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany
40 La corne d'abondance Daniel Tonini 67, avenue de l'Europe Versailles 78000 France
41 La maison d'Asie Annette Roulet 1 rue Alsace-Lorraine Toulouse 31000 France
42 Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St. Vancouver V3F 2K1 Canada
43 Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla 99362 USA
44 Lehmanns Marktstand Renate Messner Magazinweg 7 Frankfurt a.M. 60528 Germany
45 Let's Stop N Shop Jaime Yorres 87 Polk St. Suite 5 San Francisco 94117 USA
46 LILA-Supermercado Carlos González Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto 3508 Venezuela
47 LINO-Delicateses Felipe Izquierdo Ave. 5 de Mayo Porlamar I. de Margarita 4980 Venezuela
48 Lonesome Pine Restaurant Fran Wilson 89 Chiaroscuro Rd. Portland 97219 USA
49 Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo 24100 Italy
50 Maison Dewey Catherine Dewey Rue Joseph-Bens 532 Bruxelles B-1180 Belgium
51 Mère Paillarde Jean Fresnière 43 rue St. Laurent Montréal H1J 1C3 Canada
52 Morgenstern Gesundkost Alexander Feuer Heerstr. 22 Leipzig 04179 Germany
53 North/South Simon Crowther South House 300 Queensbridge London SW7 1RZ UK
54 Océano Atlántico Ltda. Yvonne Moncada Ing. Gustavo Moncada 8585 Piso 20-A Buenos Aires 1010 Argentina
55 Old World Delicatessen Rene Phillips 2743 Bering St. Anchorage 99508 USA
56 Ottilies Käseladen Henriette Pfalzheim Mehrheimerstr. 369 Köln 50739 Germany
57 Paris spécialités Marie Bertrand 265, boulevard Charonne Paris 75012 France
58 Pericles Comidas clásicas Guillermo Fernández Calle Dr. Jorge Cash 321 México D.F. 05033 Mexico
59 Piccolo und mehr Georg Pipps Geislweg 14 Salzburg 5020 Austria
60 Princesa Isabel Vinhoss Isabel de Castro Estrada da saúde n. 58 Lisboa 1756 Portugal
61 Que Delícia Bernardo Batista Rua da Panificadora, 12 Rio de Janeiro 02389-673 Brazil
62 Queen Cozinha Lúcia Carvalho Alameda dos Canàrios, 891 São Paulo 05487-020 Brazil
63 QUICK-Stop Horst Kloss Taucherstraße 10 Cunewalde 01307 Germany
64 Rancho grande Sergio Gutiérrez Av. del Libertador 900 Buenos Aires 1010 Argentina
65 Rattlesnake Canyon Grocery Paula Wilson 2817 Milton Dr. Albuquerque 87110 USA
66 Reggiani Caseifici Maurizio Moroni Strada Provinciale 124 Reggio Emilia 42100 Italy
67 Ricardo Adocicados Janete Limeira Av. Copacabana, 267 Rio de Janeiro 02389-890 Brazil
68 Richter Supermarkt Michael Holz Grenzacherweg 237 Genève 1203 Switzerland
69 Romero y tomillo Alejandra Camino Gran Vía, 1 Madrid 28001 Spain
70 Santé Gourmet Jonas Bergulfsen Erling Skakkes gate 78 Stavern 4110 Norway
71 Save-a-lot Markets Jose Pavarotti 187 Suffolk Ln. Boise 83720 USA
72 Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK
73 Simons bistro Jytte Petersen Vinbæltet 34 København 1734 Denmark
74 Spécialités du monde Dominique Perrier 25, rue Lauriston Paris 75016 France
75 Split Rail Beer & Ale Art Braunschweiger P.O. Box 555 Lander 82520 USA
76 Suprêmes délices Pascale Cartrain Boulevard Tirou, 255 Charleroi B-6000 Belgium
77 The Big Cheese Liz Nixon 89 Jefferson Way Suite 2 Portland 97201 USA
78 The Cracker Box Liu Wong 55 Grizzly Peak Rd. Butte 59801 USA
79 Toms Spezialitäten Karin Josephs Luisenstr. 48 Münster 44087 Germany
80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 México D.F. 05033 Mexico
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
83 Vaffeljernet Palle Ibsen Smagsløget 45 Århus 8200 Denmark
84 Victuailles en stock Mary Saveley 2, rue du Commerce Lyon 69004 France
85 Vins et alcools Chevalier Paul Henriot 59 rue de l'Abbaye Reims 51100 France
86 Die Wandernde Kuh Rita Müller Adenauerallee 900 Stuttgart 70563 Germany
87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland
88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA
90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland

Using the % Wildcard

The following SQL statement selects all customers with a City starting with "ber":

Example

SELECT * FROM Customers
WHERE City LIKE 'ber%';
Try it Yourself »

The following SQL statement selects all customers with a City containing the pattern "es": 

Example

SELECT * FROM Customers
WHERE City LIKE '%es%';
Try it Yourself »

Using the _ Wildcard

The following SQL statement selects all customers with a City starting with any character, followed by "ondon":

Example

SELECT * FROM Customers
WHERE City LIKE '_ondon';
Try it Yourself »

The following SQL statement selects all customers with a City starting with "L", followed by any character, followed by "n", followed by any character, followed by "on":

Example

SELECT * FROM Customers
WHERE City LIKE 'L_n_on';
Try it Yourself »

Using the [charlist] Wildcard

The following SQL statement selects all customers with a City starting with "b", "s", or "p":

Example

SELECT * FROM Customers
WHERE City LIKE '[bsp]%';
Try it Yourself »

The following SQL statement selects all customers with a City starting with "a", "b", or "c":

Example

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
Try it Yourself »

Using the [!charlist] Wildcard

The two following SQL statements select all customers with a City NOT starting with "b", "s", or "p":

Example

SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';
Try it Yourself »

Or:

Example

SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';
Try it Yourself »

Test Yourself With Exercises

Exercise:

Select all records where the second letter of the City is an "a".

SELECT * FROM Customers
WHERE City LIKE '%';

Start the Exercise


❮ Previous Next ❯