SQL LIKE Wildcard

The SQL Server LIKE Operator extracts the records whose values match the specified pattern. The Wildcard should be used along with the LIKE operator. For example, If you forget 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.

WildcardsDescription
%It represents Zero or more characters.
_It represents exactly one character.
[Char list OR Range]Select the records that 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 the LIKE operator with examples. For this demo, we use the below-shown data.

Customer Sales Table with HireDate

SQL Like wildcard % Percentage Sign

The 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%'
SQL Server LIKE Wildcard % Percentage Sign Operator

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'
SQL Like Operator Wildcard Percentage Sign Example 2

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

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Customer]
  WHERE [Occupation] LIKE N'M%t'
Using Percentage to display text start and end with characters 4

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%'
% in the middle

_ 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%'
underscore sign

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%'
SQL Server Like Operator Wildcard _ and % sign

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'_%_%_%_%'
underscore and percentage

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

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]'
Using SQL Like [] Character List

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

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]%'
Using Character List Range

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

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%'
SQL Not Like Example

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]%'
Doesn't start with either Letters

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]%'
Not starts with two Alphabets

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%'
Not Underscore and Percentage

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.

SymbolsOutput
’10[%]’10%
[_]n_n
‘[-abcd]’-, a, b, c, d
‘[[]’[
‘]’]
‘abc[_]d%’abc_d, or abc_def etc
Categories SQL