SQL LIKE Wildcard

The SQL LIKE Operator is used to extract the records whose values are matching with the 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 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

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 with examples. For this, We are going to use the below-shown data

Using % Wildcard in SQL LIKE (Percentage Sign)

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

For example, the following query returns all the Customers in the Customers table 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 Wildcard % Example 2

The following T-SQL query returns all the Customers 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 – Use % Wildcard Example 3

The following query returns 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

Like in SQL Server – 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 (Underscore Sign)

The Underscore sign (_) represents a Single character.

For example, the following T-SQL query returns all the Customers in the Customers table 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 – Use _ Wildcard Example 2

The following query returns 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 – Use _ Wildcard Example 3

The following query returns 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

SQL Like – Use _ Wildcard 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 T-SQL 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 SQL Example 2

This 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 Wildcards – Using [Character List Range]

Use this character range to search for a series of alphabets etc.

For example, this query returns all the 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 [^]

This wildcard acts as the NOT LIKE operator. The following T-SQL query returns 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 Example

The SQL wildcard allows us to use the NOT Keyword as well. For example, below query returns 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

The following T-SQL query returns 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 Example 3

Below 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 query returns all the Customers in the Customers table Whose First name doesn’t have o in the second palce.

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

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