SQL LIKE Operator and Wildcards

The SQL LIKE Operator extracts the records whose values are matching with the specified pattern. The SQL 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 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 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 Wildcards along with SQL LIKE 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 wildcard example display the Customers whose Occupation ends with Letter l.

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

The following Sql Server Like wildcard query selects the Customers Whose Occupations starts 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 SQL Server query returns the Custmers Whose Occupations contains 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 Server Like Operators _ wildcard query selects all the Customers Whose First name has Second Letter o.

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

This SQL 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 wildcard query display the Customers whose First name has at least four characters.

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

SQL Like Wildcard underscore and percentage

The following _ 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.

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

Using [] 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.

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

Name doesn't Start with either of the two alphabets

SQL Like Wildcard Example using Character List Range

Use this character range wildcard in Sql 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 [^] Example

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 SQL 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 Letter C or J.

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

The below query returns Customers 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%'

You can also use the 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