SQL LIKE Wildcard

The SQL LIKE Operator is used to extract the records whose values are matching with specified pattern. For example, If you forgot the Spelling of a Company or Product then you can use the LIKE operator along with Wildcard to retrieve the required information. Here Wildcards will be helpful to replace the missing word.

The SQL Server supports the following Wildcards

Wildcard	Description
%	It represent Zero or more characters
_	It represent exactly one character
[Char list OR Range]	Selects the records which are exactly matching with the characters or range of characters present in the square brackets
[^Char list OR Range]	Selects the records which are not matching with the characters or range of characters present in the square brackets

NOTE: In SQL Server, Wildcards should be used along with the LIKE Operator

In this article we will show you, How to use the Wildcards along with LIKE in SQL Server 2014 with examples. For this, We are going to use the below shown data

Using % Wildcard in SQL LIKE (Percentage Sign)

The percent sign (%) represents zero or more characters. For example, The following T-SQL query returns all the Customers in the Customers table Whose First name starts with Letter J.

T-SQL CODE

-- SQL Server LIKE Operator Example

SELECT [FirstName]

,[LastName]

,[YearlyIncome]

,[Education]

,[Occupation]

FROM [Customer]

WHERE [FirstName] LIKE N'J%'

OUTPUT

Using _ Wildcard in SQL LIKE (Underscore Sign)

The Underscore sign (_) represents Single character. For example, The following T-SQL query returns all the Customers in the Customers table Whose First name has Second Letter o.

T-SQL CODE

SELECT [FirstName]

,[LastName]

,[YearlyIncome]

,[Education]

,[Occupation]

FROM [Customer]

WHERE [FirstName] LIKE N'_o%'

OUTPUT

Using [] Wildcard in SQL LIKE (Character List)

The following T-SQL query returns all the Customers in the Customers table Whose First name starts with either Letter C or J.

T-SQL CODE

SELECT [FirstName]

,[LastName]

,[YearlyIncome]

,[Education]

,[Occupation]

FROM [Customer]

WHERE [FirstName] LIKE N'[CJ]%'

OUTPUT

SQL NOT LIKE Example

SQL LIKE Operator allows us to Use the NOT Keyword as well. For example, The following T-SQL query returns all the Customers in the Customers table Whose First name doesn’t starts with either Letter C or J.

T-SQL CODE

SELECT [FirstName]

,[LastName]

,[YearlyIncome]

,[Education]

,[Occupation]

FROM [Customer]

WHERE [FirstName] NOT LIKE N'[CJ]%'

OUTPUT

You can also write the above statement Using [^Character List] Wildcard.

T-SQL CODE

SELECT [FirstName]

,[LastName]

,[YearlyIncome]

,[Education]

,[Occupation]

FROM [Customer]

WHERE [FirstName] LIKE N'[^CJ]%'

OUTPUT

Using [Character List Range] Wildcard in SQL LIKE

The following T-SQL query returns all the Customers in the Customers table Whose Last name starts with any Letter between M and Y.

T-SQL CODE

SELECT [FirstName]

,[LastName]

,[YearlyIncome]

,[Education]

,[Occupation]

FROM [Customer]

WHERE [LastName] LIKE N'[M-Y]%'

OUTPUT