Line number SQL where clause placeholder

SQL wildcards


SQL wildcard characters

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

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

Wildcard Characters in MS Access

symbolDescriptionExample
*Represents zero or more charactersBl * finds bl, black, blue, and blob
?Represents a single characterh? t finds hot, hat, and hit
[]Represents any single character within the bracketsh [oa] t finds hot and hat, but not hit
!Represents any character not in the bracketsh [! oa] t finds hit, but not hot and hat
-Represents a range of charactersc [a-b] t finds cat and cbt
#Represents any single numeric character2 # 5 finds 205, 215, 225, 235, 245, 255, 265, 275, 285, and 295

Wildcard characters in SQL Server

symbolDescriptionExample
%Represents zero or more charactersbl% finds bl, black, blue, and blob
_Represents a single characterh_t finds hot, hat, and hit
[]Represents any single character within the bracketsh [oa] t finds hot and hat, but not hit
^Represents any character not in the bracketsh [^ oa] t finds hit, but not hot and hat
-Represents a range of charactersc [a-b] t finds cat and cbt

All the wildcards can also be used in combinations!

Here are some examples showing different operators with '%' and '_' wildcards:

LIKE operatorDescription
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
1Alfred's feed boxMaria 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
6Blue lake delicaciesHanna MoosForsterstrasse 57Mannheim68306Germany
7Blondel père et filsFrederique Citeaux24, place KléberStrasbourg67000France
8Bólido Comidas preparadasMartín summerC / 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 WangMain street 29Bern3012Switzerland
15Comércio MineiroPedro AfonsoAv. dos Lusíadas, 23São Paulo05432-043Brazil
16Consolidated HoldingsElizabeth BrownBerkeley Gardens 12 BreweryLondonWX1 6LTUK
17Dragon's Blood DeliciousSven OttliebWalserweg 21Aachen52066Germany
18You moons entierJanine Labrune67, rue des Cinquante OtagesNantes44000France
19Eastern ConnectionAnn Devon35 King GeorgeLondonWX3 6FWUK
20Serious tradeRoland MendelKirchgasse 6Graz8010Austria
21Familia ArquibaldoAria CruzRua Orós, 92São Paulo05442-030Brazil
22FISSA Fabrica Inter. Salchichas S.A.Diego RoelC / Moralzarzal, 86Madrid28034Spain
23Folies gourmandesMartine Rancé184, chaussée de TournaiLille59000France
24Folk och fä HBMaria LarssonÅkergatan 24BräckeS-844 67Sweden
25Swiss franc shippingPeter FranconiaBerliner Platz 43Munich80805Germany
26France restorationCarine Schmitt54, rue RoyaleNantes44000France
27Franchi S.p.A.Paolo AccortiVia Monte Bianco 34Torino10100Italy
28Furia Bacalhau e Frutos do MarLino RodriguezJardim das rosas n.32Lisboa1675Portugal
29Gastronome GalleryEduardo SaavedraRambla de Cataluña, 23Barcelona08022Spain
30Godos Cocina TípicaJosé Pedro FreyreC / Romero, 33Seville41101Spain
31Gourmet LanchonetesAndré FonsecaAv. Brazil, 442Campinas04876-786Brazil
32Great Lakes Food MarketHoward Snyder2732 Baker Blvd.Eugene97403United States
33GROSELLA restaurantsManuel Pereira5ª Ave. Los Palos GrandesCaracas1081Venezuela
34Hanari CarnesMario PontesRua do Paço, 67Rio de Janeiro05454-876Brazil
35HILARIÓN abastosCarlos HernándezCarrera 22 con Ave. Carlos Soublette # 8-35San Cristobal5022Venezuela
36Hungry Coyote Import StoreYoshi LatimerCity Center Plaza 516 Main St.Elgin97827United States
37Hungry Owl All-Night GrocersPatricia McKenna8 Johnstown RoadCorkIreland
38Island TradingHelen BennettGarden House Crowther WayCowesPO31 7PJUK
39Royal foodPhilip CramerMaubelstrasse 90Brandenburg14776Germany
40La corne d'abondanceDaniel Tonini67, avenue de l'EuropeVersailles78000France
41La maison d'AsieAnnette Roulet1 rue Alsace-LorraineToulouse31000France
42Laughing Bacchus Wine CellarsYoshi Tannamuri1900 Oak St.VancouverV3F 2K1Canada
43Lazy K Kountry StoreJohn Steel12 Orchestra TerraceWalla Walla99362United States
44Lehmann's market stallRenate MessnerMagazine path 7Frankfurt a. M. 60528Germany
45Let's Stop N ShopJaime Yorres87 Polk St. Suite 5San Francisco94117United States
46PURPLE super mercadoCarlos GonzálezCarrera 52 con Ave. Bolívar # 65-98 Llano LargoBarquisimeto3508Venezuela
47LINO delicatesesFelipe IzquierdoAve. 5 de Mayo PorlamarI. de Margarita4980Venezuela
48Lonesome Pine RestaurantFran Wilson89 Chiaroscuro Rd.Portland97219United States
49Magazzini Alimentari RiunitiGiovanni RovelliVia Ludovico il Moro 22Bergamo24100Italy
50Maison DeweyCatherine DeweyRue Joseph-Bens 532BruxellesB-1180Belgium
51Mère PaillardeJean Fresnière43 rue St. LaurentMontrealH1J 1C3Canada
52Morgenstern health foodAlexander fireHeerstr. 22ndLeipzig04179Germany
53North / SouthSimon CrowtherSouth House 300 QueensbridgeLondonSW7 1RZUK
54Océano Atlántico Ltda.Yvonne MoncadaIng.Gustavo Moncada 8585 Piso 20-ABuenos Aires1010Argentina
55Old World DelicatessenRene Phillips2743 Bering St.Anchorage99508United States
56Ottilie's cheese shopHenriette PfalzheimMehrheimerstr. 369Cologne50739Germany
57Paris specialitesMarie Bertrand265, boulevard CharonneParis75012France
58Pericles Comidas clásicasGuillermo FernándezCalle Dr. Jorge cash 321México D.F.05033Mexico
59Piccolo and moreGeorg PippsGeislweg 14Salzburg5020Austria
60Princesa Isabel VinhossIsabel de CastroEstrada da saúde n.58Lisboa1756Portugal
61Que DelíciaBernardo BatistaRua da Panificadora, 12Rio de Janeiro02389-673Brazil
62Queen CozinhaLúcia CarvalhoAlameda dos Canàrios, 891São Paulo05487-020Brazil
63QUICK stopHorst KlossTaucherstraße 10Cunewalde01307Germany
64Rancho grandeSergio GutiérrezAv. del Libertador 900Buenos Aires1010Argentina
65Rattlesnake Canyon GroceryPaula Wilson2817 Milton Dr.Albuquerque87110United States
66Reggiani CaseificiMaurizio MoroniStrada Provinciale 124Reggio Emilia42100Italy
67Ricardo AdocicadosJanete LimeiraAv. Copacabana, 267Rio de Janeiro02389-890Brazil
68Judge supermarketMichael WoodGrenzacherweg 237Geneva1203Switzerland
69Romero y tomilloAlejandra CaminoGran Vía, 1Madrid28001Spain
70Santé gourmetJonas BergulfsenErling Skakkes gate 78Stavern4110Norway
71Save-a-lot MarketsJose Pavarotti187 Suffolk Ln.Boise83720United States
72Seven Seas ImportsHari Kumar90 Wadhurst Rd.LondonOX15 4NBUK
73Simons bistroJytte PetersenVinbæltet 34København1734Denmark
74Specialities du mondeDominique Perrier25, rue LauristonParis75016France
75Split Rail Beer & AleKind of BrunswickP.O. Box 555Countries82520United States
76Suprêmes délicesPascale CartrainBoulevard Tirou, 255CharleroiB-6000Belgium
77The Big CheeseLiz Nixon89 Jefferson Way Suite 2Portland97201United States
78The Cracker BoxLiu Wong55 Grizzly Peak Rd.Butte59801United States
79Tom's specialtiesKarin JosephsLuisenstrasse 48Muenster44087Germany
80Tortuga RestauranteMiguel Angel PaolinoAvda. Azteca 123México D.F.05033Mexico
81Tradição HipermercadosAnabela DominguesAv. Inês de Castro, 414São Paulo05634-030Brazil
82Trail's Head Gourmet ProvisionersHelvetius Nagy722 DaVinci Blvd.Kirkland98034United States
83VaffeljernetPalle IbsenSmagsløget 45Aarhus8200Denmark
84Victuailles en stockMary Saveley2, rue du CommerceLyon69004France
85Vins et alcools ChevalierPaul Henriot59 rue de l'AbbayeReims51100France
86The wandering cowRita MuellerAdenauerallee 900Stuttgart70563Germany
87Wartian HerkkuPirkko KoskitaloTorikatu 38Oulu90110Finland
88Wellington ImportadoraPaula ParenteRua do Mercado, 12Resende08737-363Brazil
89White Clover MarketsKarl Jablonski305-14th Ave. S. Suite 3BSeattle98128United States
90Wilman KalaMatti KarttunenKeskuskatu 45Helsinki21240Finland
91WolskiZbyszekFiltrowa 68 ulWalla01-012Poland


Using the% wildcard

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

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


Using the _ wildcard

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

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":


Using the [charlist] wildcard

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

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


Using the [! Charlist] wildcard

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

Or: