SQL LIKE

The SQL LIKE Operator extracts the records whose values are matching with the specified pattern. In SQL Server, Wildcard should use 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

Let us see how to use the Wildcards along with LIKE in SQL Server with examples. For this Like operator demo, we 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%'

SQL Like Operator 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'

SQL Server Like % Operator 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'

SQL Server Like Operator Use % Wildcard Example 4

The below SQL Server 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%'

_ Wildcard in SQL LIKE Operator (Underscore Sign)

The Underscore sign (_) represents a Single character. The following _ wildcard query selects 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%'

SQL Like Operator _ Wildcard Example 2

This SQL wildcard query displays the Customers whose Yearly income contains 00 at the second and third positions.

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

SQL Like Operator _ Wildcard Example 3

This 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'_%_%_%_%'

Use _ Wildcard in SQL Like Operator Example 4

The following _ wildcard query returns the Customers whose Yearly income starts with 8 and ends with 0. Here, we used three _ signs means, and 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'

Using [] Wildcard in LIKE Character List

Use [] (character list) to search for multiple items. It 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]'

[] 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]%'

SQL Wildcard Example using Character List Range

Use this character range in Sql Server like operator to search for a series of alphabets etc. This wildcard 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]%'

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]%'

SQL NOT LIKE Operator Example

The SQL LIKE operator 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%'

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

SQL NOT LIKE Operator Example 2

This SQL Not Like statement 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]%'

SQL NOT LIKE Operator Example 3

Below Sql Server Not Like operator 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]%'

SQL NOT LIKE Example 4

This Sql Server Not Like statement selects 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%'

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

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