Christmas Special : Upto 40% OFF! + 2 free courses - SCHEDULE CALL

Select Course
Resources

(4.8/5 ) | 1.5K+ Ratings

sddsfsf

× ×

SQL

SQL Wildcards- How to Use Wildcards in SQL Server

A wild card in card games may be used to represent any other playing card, sometimes with certain restrictions. These may be jokers, for example, in Rummy games, or ordinary ranked and suited cards may be designated as wild cards, such as the Joker of Club and 9 of diamonds in Classic Brag or the "deuces wild" in Poker.

Similar to card games, there are operators or characters in SQL Server which can represent or replace or take the place of other characters. These are called wild card characters. Over the next few paragraphs, we will learn about these different types of wild card characters in SQL Server. Understanding the SQL wildcards begin with the understanding of SQL Server; you can get an insight about the same through our online SQL server training.

What are Wild Card Characters?

A wildcard character substitutes 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.

Different Types of Wildcard Characters in Sql Server

SQL support two wildcard operators in conjunction with the LIKE operator, which are explained in detail in the following table.

Wildcard

Description

The percent sign (%)

Matches one or more characters.

Note − MS Access uses the asterisk (*) wildcard character instead of the percent sign (%) wildcard character.

The underscore (_)

Matches one character.

Note − MS Access uses a question mark (?) instead of the underscore (_) to match any one character.

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or a character. These symbols can be used in combinations.

Different Types of 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 a hot, hat and hit
[]	Represents any single character within the brackets	h[oa]t finds hot and hat but not heat
!	Represents any character not in the brackets	h[!oat] 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 start with “a”
Where CustomerName LIKE ‘%a’	Finds any values that end 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 start with “a” and are at least 3 characters long.
Where ContactName like ‘a%o’	Finds any values that start with “a” and end with “o”

The following image gives an idea about the use of wildcard characters. If you’re someone who wants to make a career in SQL, learn about SQL developer salary and the top companies hiring SQL developers around the world.

Dummy Table for Wildcard Testing

Let us create a dummy table for wildcard testing. The syntax for creating the table is as follows

CREATE TABLE [dbo].[Dummy Table](
[CustomerId] [int] NULL,
[CustomerName] [varchar](100) NULL,
[Contactname] [varchar](100) NULL,
[Address] [varchar](50) NULL,
[City] [varchar](20) NULL,
[Postalcode] [int] NULL,
[Country] [varchar](50) NULL
) ON [PRIMARY]

The SQL Statement to enter data into the table is as below

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (1, N'Alfreds Futterkiste', N'Maria Anders', N'Obere Str. 57', N'Berlin', 12209, Germany)

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (2, N'Ana Trujillo Emparedados y helados', N'Ana Trujillo', N'Avda. de la Constitución 2222', N'México D.F.', 5021, N'Mexico')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (3, N'Antonio Moreno Taquería', N'Antonio Moreno', N'Mataderos 2312', N'México D.F.', 5023, N'Mexico')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (4, N'Around the Horn', N'Thomas Hardy', N'120 Hanover Sq.', N'London', NULL, N'UK')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (5, N'Berglunds snabbköp', N'Christina Berglund', N'Berguvsvägen 8', N'Luleå', NULL, N'Sweden')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (6, N'Blauer See Delikatessen', N'Hanna Moos', N'Forsterstr. 57', N'Mannheim', 68306, Germany)

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (7, N'Blondel père et fils', N'Frédérique Citeaux', N'24, place Kléber', N'Strasbourg', 67000, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (8, N'Bólido Comidas preparadas', N'Martín Sommer', N'C/ Araquil, 67', N'Madrid', 28023, N'Spain')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (9, N'Bon app''', N'Laurence Lebihans', N'12, rue des Bouchers', N'Marseille', 13008, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (10, N'Bottom-Dollar Marketse', N'Elizabeth Lincoln', N'23 Tsawassen Blvd.', N'Tsawassen', NULL, N'Canada')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (11, N'B''s Beverages', N'Victoria Ashworth', N'Fauntleroy Circus', N'London', NULL, N'UK')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (12, N'Cactus Comidas para llevar', N'Patricio Simpson', N'Cerrito 333', N'Buenos Aires', 1010, N'Argentina')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (13, N'Centro comercial Moctezuma', N'Francisco Chang', N'Sierras de Granada 9993', N'México D.F.', 5022, N'Mexico')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (14, N'Chop-suey Chinese', N'Yang Wang', N'Hauptstr. 29', N'Bern', 3012, N'Switzerland')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (15, N'Comércio Mineiro', N'Pedro Afonso', N'Av. dos Lusíadas, 23', N'São Paulo', NULL, N'Brazil')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (16, N'Consolidated Holdings', N'Elizabeth Brown', N'Berkeley Gardens 12 Brewery', N'London', NULL, N'UK')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (17, N'Drachenblut Delikatessend', N'Sven Ottlieb', N'Walserweg 21', N'Aachen', 52066, N'Germany')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (18, N'Du monde entier', N'Janine Labrune', N'67, rue des Cinquante Otages', N'Nantes', 44000, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (19, N'Eastern Connection', N'Ann Devon', N'35 King George', N'London', NULL, N'UK')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (20, N'Ernst Handel', N'Roland Mendel', N'Kirchgasse 6', N'Graz', 8010, N'Austria')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (21, N'Familia Arquibaldo', N'Aria Cruz', N'Rua Orós, 92', N'São Paulo', NULL, N'Brazil')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (22, N'FISSA Fabrica Inter. Salchichas S.A.', N'Diego Roel', N'C/ Moralzarzal, 86', N'Madrid', 28034, N'Spain')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (23, N'Folies gourmandes', N'Martine Rancé', N'184, chaussée de Tournai', N'Lille', 59000, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (24, N'Folk och fä HB', N'Maria Larsson', N'Åkergatan 24', N'Bräcke', NULL, N'Sweden')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (25, N'Frankenversand', N'Peter Franken', N'Berliner Platz 43', N'München', 80805, N'Germany')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (26, N'France restauration', N'Carine Schmitt', N'54, rue Royale', N'Nantes', 44000, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (27, N'Franchi S.p.A.', N'Paolo Accorti', N'Via Monte Bianco 34', N'Torino', 10100, N'Italy')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (28, N'Furia Bacalhau e Frutos do Mar', N'Lino Rodriguez', N'Jardim das rosas n. 32', N'Lisboa', 1675, N'Portugal')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (29, N'Galería del gastrónomo', N'Eduardo Saavedra', N'Rambla de Cataluña, 23', N'Barcelona', 8022, N'Spain')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (30, N'Godos Cocina Típica', N'José Pedro Freyre', N'C/ Romero, 33', N'Sevilla', 41101, N'Spain')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (31, N'Gourmet Lanchonetes', N'André Fonseca', N'Av. Brasil, 442', N'Campinas', NULL, N'Brazil')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (32, N'Great Lakes Food Market', N'Howard Snyder', N'2732 Baker Blvd.', N'Eugene', 97403, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (33, N'GROSELLA-Restaurante', N'Manuel Pereira', N'5ª Ave. Los Palos Grandes', N'Caracas', 1081, N'Venezuela')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (34, N'Hanari Carnes', N'Mario Pontes', N'Rua do Paço, 67', N'Rio de Janeiro', NULL, N'Brazil')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (35, N'HILARIÓN-Abastos', N'Carlos Hernández', N'Carrera 22 con Ave. Carlos Soublette #8-35', N'San Cristóbal', 5022, N'Venezuela')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (36, N'Hungry Coyote Import Store', N'Yoshi Latimer', N'City Center Plaza 516 Main St.', N'Elgin', 97827, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (37, N'Hungry Owl All-Night Grocers', N'Patricia McKenna', N'8 Johnstown Road', N'Cork', NULL, N'Ireland')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (38, N'Island Trading', N'Helen Bennett', N'Garden House Crowther Way', N'Cowes', NULL, N'UK')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (39, N'Königlich Essen', N'Philip Cramer', N'Maubelstr. 90', N'Brandenburg', 14776, N'Germany')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (40, N'La corne d''abondance', N'Daniel Tonini', N'67, avenue de l''Europe', N'Versailles', 78000, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (41, N'La maison d''Asie', N'Annette Roulet', N'1 rue Alsace-Lorraine', N'Toulouse', 31000, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (42, N'Laughing Bacchus Wine Cellars', N'Yoshi Tannamuri', N'1900 Oak St.', N'Vancouver', NULL, N'Canada')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (43, N'Lazy K Kountry Store', N'John Steel', N'12 Orchestra Terrace', N'Walla Walla', 99362, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (44, N'Lehmanns Marktstand', N'Renate Messner', N'Magazinweg 7', N'Frankfurt a.M.', 60528, N'Germany')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (45, N'Let''s Stop N Shop', N'Jaime Yorres', N'87 Polk St. Suite 5', N'San Francisco', 94117, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (46, N'LILA-Supermercado', N'Carlos González', N'Carrera 52 con Ave. Bolívar #65-98 Llano Largo', N'Barquisimeto', 3508, N'Venezuela')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (47, N'LINO-Delicateses', N'Felipe Izquierdo', N'Ave. 5 de Mayo Porlamar', N'I. de Margarita', 4980, N'Venezuela')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (48, N'Lonesome Pine Restaurant', N'Fran Wilson', N'89 Chiaroscuro Rd.', N'Portland', 97219, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (49, N'Magazzini Alimentari Riuniti', N'Giovanni Rovelli', N'Via Ludovico il Moro 22', N'Bergamo', 24100, N'Italy')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (50, N'Maison Dewey', N'Catherine Dewey', N'Rue Joseph-Bens 532', N'Bruxelles', NULL, N'Belgium')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (51, N'Mère Paillarde', N'Jean Fresnière', N'43 rue St. Laurent', N'Montréal', NULL, N'Canada')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (52, N'Morgenstern Gesundkost', N'Alexander Feuer', N'Heerstr. 22', N'Leipzig', 4179, N'Germany')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (53, N'North/South', N'Simon Crowther', N'South House 300 Queensbridge', N'London', NULL, N'UK')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (54, N'Océano Atlántico Ltda.', N'Yvonne Moncada', N'Ing. Gustavo Moncada 8585 Piso 20-A', N'Buenos Aires', 1010, N'Argentina')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (55, N'Old World Delicatessen', N'Rene Phillips', N'2743 Bering St.', N'Anchorage', 99508, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (56, N'Ottilies Käseladen', N'Henriette Pfalzheim', N'Mehrheimerstr. 369', N'Köln', 50739, N'Germany')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (57, N'Paris spécialités', N'Marie Bertrand', N'265, boulevard Charonne', N'Paris', 75012, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (58, N'Pericles Comidas clásicas', N'Guillermo Fernández', N'Calle Dr. Jorge Cash 321', N'México D.F.', 5033, N'Mexico')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (59, N'Piccolo und mehr', N'Georg Pipps', N'Geislweg 14', N'Salzburg', 5020, N'Austria')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (60, N'Princesa Isabel Vinhoss', N'Isabel de Castro', N'Estrada da saúde n. 58', N'Lisboa', 1756, N'Portugal')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (61, N'Que Delícia', N'Bernardo Batista', N'Rua da Panificadora, 12', N'Rio de Janeiro', NULL, N'Brazil')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (62, N'Queen Cozinha', N'Lúcia Carvalho', N'Alameda dos Canàrios, 891', N'São Paulo', NULL, N'Brazil')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (63, N'QUICK-Stop', N'Horst Kloss', N'Taucherstraße 10', N'Cunewalde', 1307, N'Germany')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (64, N'Rancho grande', N'Sergio Gutiérrez', N'Av. del Libertador 900', N'Buenos Aires', 1010, N'Argentina')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (65, N'Rattlesnake Canyon Grocery', N'Paula Wilson', N'2817 Milton Dr.', N'Albuquerque', 87110, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (66, N'Reggiani Caseifici', N'Maurizio Moroni', N'Strada Provinciale 124', N'Reggio Emilia', 42100, N'Italy')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (67, N'Ricardo Adocicados', N'Janete Limeira', N'Av. Copacabana, 267', N'Rio de Janeiro', NULL, N'Brazil')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (68, N'Richter Supermarkt', N'Michael Holz', N'Grenzacherweg 237', N'Genève', 1203, N'Switzerland')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (69, N'Romero y tomillo', N'Alejandra Camino', N'Gran Vía, 1', N'Madrid', 28001, N'Spain')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (70, N'Santé Gourmet', N'Jonas Bergulfsen', N'Erling Skakkes gate 78', N'Stavern', 4110, N'Norway')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (71, N'Save-a-lot Markets', N'Jose Pavarotti', N'187 Suffolk Ln.', N'Boise', 83720, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (72, N'Seven Seas Imports', N'Hari Kumar', N'90 Wadhurst Rd.', N'London', NULL, N'UK')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (73, N'Simons bistro', N'Jytte Petersen', N'Vinbæltet 34', N'København', 1734, N'Denmark')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (74, N'Spécialités du monde', N'Dominique Perrier', N'25, rue Lauriston', N'Paris', 75016, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (75, N'Split Rail Beer & Ale', N'Art Braunschweiger', N'P.O. Box 555', N'Lander', 82520, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (76, N'Suprêmes délices', N'Pascale Cartrain', N'Boulevard Tirou, 255', N'Charleroi', NULL, N'Belgium')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (77, N'The Big Cheese', N'Liz Nixon', N'89 Jefferson Way Suite 2', N'Portland', 97201, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (78, N'The Cracker Box', N'Liu Wong', N'55 Grizzly Peak Rd.', N'Butte', 59801, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (79, N'Toms Spezialitäten', N'Karin Josephs', N'Luisenstr. 48', N'Münster', 44087, N'Germany')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (80, N'Tortuga Restaurante', N'Miguel Angel Paolino', N'Avda. Azteca 123', N'México D.F.', 5033, N'Mexico')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (81, N'Tradição Hipermercados', N'Anabela Domingues', N'Av. Inês de Castro, 414', N'São Paulo', NULL, N'Brazil')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (82, N'Trail''s Head Gourmet Provisioners', N'Helvetius Nagy', N'722 DaVinci Blvd.', N'Kirkland', 98034, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (83, N'Vaffeljernet', N'Palle Ibsen', N'Smagsløget 45', N'Århus', 8200, N'Denmark')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (84, N'Victuailles en stock', N'Mary Saveley', N'2, rue du Commerce', N'Lyon', 69004, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (85, N'Vins et alcools Chevalier', N'Paul Henriot', N'59 rue de l''Abbaye', N'Reims', 51100, N'France')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (86, N'Die Wandernde Kuh', N'Rita Müller', N'Adenauerallee 900', N'Stuttgart', 70563, N'Germany')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (87, N'Wartian Herkku', N'Pirkko Koskitalo', N'Torikatu 38', N'Oulu', 90110, N'Finland')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (88, N'Wellington Importadora', N'Paula Parente', N'Rua do Mercado, 12', N'Resende', NULL, N'Brazil')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (89, N'White Clover Markets', N'Karl Jablonski', N'305 - 14th Ave. S. Suite 3B', N'Seattle', 98128, N'USA')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (90, N'Wilman Kala', N'Matti Karttunen', N'Keskuskatu 45', N'Helsinki', 21240, N'Finland')

INSERT [dbo].[Dummy Table] ([CustomerId], [CustomerName], [Contactname], [Address], [City], [Postalcode], [Country]) VALUES (91, N'Wolski', N'Zbyszek', N'ul. Filtrowa 68', N'Walla', NULL, N'Poland')

The Output Looks Like Below

Using The % Wildcard

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

SELECT * FROM [dbo].[Dummy Table]
WHERE City LIKE 'ber%';

The Output is As Below

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

SELECT * FROM [dbo].[Dummy Table]
WHERE City LIKE '%es%';

The Output Looks Like Below

Using The [charlist] Wildcard

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

SELECT * FROM Customers
WHERE City LIKE '[bsp]%';

The Output Looks Like Below

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

SELECT * FROM [dbo].[Dummy Table]
WHERE City LIKE '[a-c]%';

The Output Looks Like Below

Using The [!charlist] Wildcard

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

SELECT * FROM [dbo].[Dummy Table]
WHERE City LIKE '[!bsp]%';

The Output Looks Like Below

SELECT * FROM [dbo].[Dummy Table]
WHERE City NOT LIKE '[bsp]%';

The Output Looks Like The Below

Advantages of Wild Card Character

Wildcards are special characters that can stand in for unknown characters in a text value and are handy for locating multiple items with similar but not identical data. Wildcards can also help with getting data based on a specified pattern match.

Disadvantages of Wild Card Character

The biggest drawback is performance. Using wildcards at the front of your search string forces the query to read every row in the table. There's no way to speed it up with simple B-tree indexes. As your table grows, this query will get proportionally slower.

Conclusion

The above write-up discussed ard characters in SQL Server. It discusses the use of different wild card characters. It also discusses the use of wild card characters and its advantages and disadvantages. This will generate enough interest for the readers to study more about these functionalities. Let's dive more into the topic of SQL wildcard characters and learn more about their importance in SQL and key takeaways. You should check out the SQL career path guide to help you explore all your career options.

SQL Training For Administrators & Developers