SQL LIKE Wildcard Operator

The SQL LIKE Operator is used to extract the records whose values are matching with the specified pattern. In SQL Server, Wildcard should be used along with the LIKE Operator

For example, If you forgot the Spelling of a Company or Product then you can use the Sql Server 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 Sql Server Wildcards

SQL Wildcards	Description
%	It represents Zero or more characters
_	It represents 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

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

SQL Like Wildcard % (Percentage Sign)

The SQL Wildcard percentage sign (%) represents zero or more characters.

For example, the following wildcard query returns all the Customers whose First name starts with Letter J.

-- SQL Server LIKE Example
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'J%'

OUTPUT

SQL Like Wildcard % Example 2

This wildcard example display the Customers whose Occupation ends with Letter l.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [Occupation] LIKE N'%l'

OUTPUT

SQL Server Like Wildcard % Example 3

The following Sql Server like wildcard query selects the Customers Whose Occupations starts with Letter M and ends with t.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [Occupation] LIKE N'M%t'

OUTPUT

SQL Server Like Operator Use % Wildcard Example 4

The below query returns the Customers Whose Occupations contains Ma at any position.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [Occupation] LIKE N'%Ma%'

OUTPUT

Using _ Wildcard in SQL LIKE Operator (Underscore Sign)

The Underscore sign (_) represents a Single character.

For example, the following T-SQL query select all the Customers Whose First name has Second Letter o.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'_o%'

OUTPUT

SQL Like Operator – Use _ Wildcard Example 2

The following SQL wildcard query display the Customers whose Yearly income contains 00 at the second and third position.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [YearlyIncome] LIKE N'_00%'

OUTPUT

SQL Like Operator – Use _ Wildcard Example 3

The below SQL wildcard query display the Customers whose First name has at least four characters.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'_%_%_%_%'

OUTPUT

Use _ Wildcard in SQL Like Operator Example 4

The following query returns the Customers whose Yearly income starts with 8 and ends with 0. Here, we used three _ signs means, it will accept three numbers or words between 8 and 0.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [YearlyIncome] LIKE N'8___0'

OUTPUT

Using [] Wildcard in SQL Server LIKE (Character List)

Use [] (character list) to search for multiple items. The following query returns all the Customers in the Customers table Whose First name starts with either Letter C or J.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'[CJ]'

OUTPUT

Use [] Wildcard in Like Operator Example 2

This Sql Server like operator query returns the Customers whose First name does not start with either C or J.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'[^CJ]%'

OUTPUT

SQL Wildcard Example using [Character List Range]

Use this character range in Sql Server like operator to search for a series of alphabets etc.

For example, this query returns all Customers from the Customers table whose Last name starts with any Letter between M and Y.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [LastName] LIKE N'[M-Y]%'

OUTPUT

SQL Wildcard [^] Example

This wildcard acts as the NOT LIKE operator. The following query displays the Customers whose Last name doesn’t start with any Letter between M and Y.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [LastName] LIKE N'[^M-Y]%'

OUTPUT

SQL NOT LIKE Operator Example

The SQL wildcard allows us to use the NOT Keyword as well. For example, below SQL server not like query shows the Customers whose First name doesn’t start with J.

-- SQL Server NOT LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] NOT LIKE N'J%'

OUTPUT

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

SQL NOT LIKE Example 2

This SQL Not Like query shows the Customers whose First name doesn’t start with either Letter C or J.

-- SQL Server NOT LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] NOT LIKE N'[CJ]%'

OUTPUT

SQL NOT LIKE Operator Example 3

Below Sql Server Not Like query returns Customers table records whose Last name doesn’t start with Letters between M or Z.

-- SQL Server NOT LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [LastName] NOT LIKE N'[M-Z]%'

OUTPUT

SQL NOT LIKE Example 4

The following Sql Server Not Like query select the Customers from the Customers table Whose First name doesn’t have o in the second place.

-- SQL Server LIKE Example
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] NOT LIKE N'_o%'

OUTPUT

You can also use the Wildcard characters like %, _ as the literal characters too. However, you have to do some extra work to achieve this.

The following table will show you some examples of SQL wildcard:

SQL Wildcard Symbols	Output
LIKE ’10[%]’	10%
[_]n	_n
LIKE ‘[-abcd]’	-, a, b, c, d
LIKE ‘[[]’	[
LIKE ‘]’	]
LIKE ‘abc[_]d%’	abc_d, or abc_def etc