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 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%'
OUTPUT
SQL Like 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'
OUTPUT
SQL Server Like % 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'
OUTPUT
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%'
OUTPUT
_ Wildcard in SQL LIKE Operator (Underscore Sign)
The Underscore sign (_) represents a Single character.
For example, 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%'
OUTPUT
SQL Like Operator _ Wildcard Example 2
The following 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%'
OUTPUT
SQL Like Operator _ Wildcard Example 3
The below SQL 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'_%_%_%_%'
OUTPUT
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'
OUTPUT
Using [] Wildcard in LIKE Character List
Use [] (character list) to search for multiple items. The following 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
[] 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]%'
OUTPUT
SQL Wildcard Example using Character List Range
Use this character range in Sql Server like operator to search for a series of alphabets etc.
For example, this 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]%'
OUTPUT
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]%'
OUTPUT
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%'
OUTPUT
You can also write the above statement Using [^Character List] Wildcard.
SQL NOT LIKE Example 2
This SQL Not Like query 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]%'
OUTPUT
SQL NOT LIKE Operator Example 3
Below Sql Server Not Like 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
This Sql Server Not Like query 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%'
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 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 |