SQL LIKE

The SQL Server LIKE Operator extracts the records whose values match the specified pattern. The Wildcard should use along with the SQL LIKE operator. For example, If you forgot the Spelling of a Company or Product, you can use the operator and wildcard to retrieve the required information.

Here Wildcards will be helpful to replace the missing word. The SQL Server supports the following LIKE Wildcards.

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 SQL Wildcards along with the LIKE operator with examples. For this demo, we use the below-shown data.

SQL Like wildcard % Percentage Sign

The SQL Like Wildcard percentage sign (%) represents zero or more characters. For example, the following % query returns all the Customers whose First name starts with Letter J.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [FirstName] LIKE N'J%'

This example displays the Customers whose Occupation ends with the Letter l.

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

The following Sql Server Like operator wildcard query selects the Customers Whose Occupations start with Letter M and ends with t.

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

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

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

_ Wildcard Underscore Sign

The Underscore sign (_) represents a Single character.

The following SQL Like Operators _ wildcard query selects all the Customers Whose First name has a Second Letter o.

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

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

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

This query displays the Customers whose First name has at least four characters.

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

The following SQL Like operator wildcard _ query returns the Customers whose Yearly income starts with 8 and ends with 0. Here, we used three _ sign means, which will accept three numbers or words between 8 and 0.

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

SQL Server LIKE Wildcard [] Character List

Use LIke [] (character list) wildcard to search for multiple items. For example, it returns all the Customers in the Customers table Whose First name starts with either the Letter C or J.

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

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

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

SQL Like Wildcard Example using Character List Range

Use this character range wildcard Like operator to search for a series of alphabets etc. This query returns all Customers from the Customers table whose Last name starts with any Letter between M and Y.

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

SQL Like Wildcard [^] NOT

It acts as the NOT operator.

The following query displays the Customers whose Last name doesn’t start with any Letter between M and Y.

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

SQL NOT LIKE Operator Example

This Like operator allows us to use the NOT Keyword as well. For example, the below query shows the Customers whose First name doesn’t start with J.

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. This statement shows the Customers whose First name doesn’t start with either Letters C or J.

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

The below query returns the Customer’s table records whose Last name doesn’t start with Letters between M or Z.

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

This statement selects the Customers from the Customers table Whose First name doesn’t have o in the second place.

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

Extra Wilcard Symbols

You can also use the Like Wildcard characters %, _ as the literal characters too. However, you have to do some extra work to achieve this. The following table will show you some examples.

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