SQL Wildcard Characters with Example

In this blog, we will learn what is SQL Wildcard Characters how to use in SQL query for searching data.

We can use SQL Wildcard character in SQL Query to find out particular result set from database table With 'LIKE' Operator.Following are the list of SQL Wildcard characters.



To understand it better lets create 1 dummy table in database.

/****** Object: Table [dbo].[Register] Script Date: 30-11-2015 18:26:51 ******/
SET
ANSI_NULLS ON GO
SET
QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Register](
[RegId] [bigint] NOT NULL,
[FirstName] [nvarchar](100) NULL,
[LastName] [nvarchar](100) NULL,
[Email] [nvarchar](100) NULL,
[Mobile] [nvarchar](15) NULL CONSTRAINT [PK_Register] PRIMARY KEY CLUSTERED ([RegId] ASC) WITH (
PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY] GO

Insert dummy records in table:

INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])
VALUES(1, 'Ankur', 'Mistry', '[email protected]', '9321453xxx')
INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])
VALUES(2, 'Rahul', 'Lad', '[email protected]', '9321453xxx')
INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])
VALUES(3, 'Nikhil', 'Rajput', '[email protected]', '9321453xxx')
INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])
VALUES(4, 'Bhal', 'Mithbavkar', '[email protected]', '9321453xxx')
INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])
VALUES(5, 'Rushabh', 'Parekh', '[email protected]', '9321453xxx')
INSERT INTO[dbo].[Register]([RegId], [FirstName], [LastName], [Email], [Mobile])
VALUES(6, 'Chaitali', 'Shah', '[email protected]', '9321453xxx')

I have records as per below screen.



Query with SQL Wildcard '%'



Query 1: Will return the records starting with 'an'.

Query 2: Will return the records which containing string 'ha'.

Query with SQL Wildcard '_'



Query 1: will return result set start with any character, followed by 'ushabh'.

Query 2: will return result set start with any character followed by ah and then again any character followed by 'l'.

Query with SQL Wildcard []'



Query 1: Will check the firstname starting with 'a' 'r' 'c'.

Query 2: Will check firstname range 'a' 'b' 'c'.

Query with SQL Wildcard '[^]' or '[!]'



Query 1: Will check the firstname NOT starting with 'r' 'c' ( LIKE [^]).

Query 2: Will check the firstname NOT starting with 'r' 'c' (NOT LIKE [!]).

Please check attached file for table and SQL query scripts.