Hello Friends, In this tutorial we are going to Learn Operator in SQL Server. Like Operator is used to perform a pattern matching search in a Query.

Like Operator in SQL Server:

Like Operator is used to perform a pattern matching search in a Query. Like Operator is used in the where clause of select, Insert, update and delete statement.

Syntax:

The Syntax for using Like Operator in SQL Server.

expression LIKE pattern [ ESCAPE ‘escape_character’ ]

Where;

expression specified columns on which you want to perform Pattern matching search.

pattern specified the wild cards. There is two wild card used with like operator:
%: allows you to match string of any length (including zero)
_: allows you to match on a single character.

ESCAPE ‘escape_character’
Optional. It allows you to pattern match on literal instances of a wildcard character such as % or _.

Like Operator with SELECT Statement:

First of all create a table and insert few demo Records:

Create Table:

CREATE TABLE [dbo].[Employee](
[EmployeeID] [bigint] IDENTITY(1,1) NOT NULL,
[EmployeeName] [varchar](100) NULL,
[Designation] [varchar](50) NULL,
[Salary] [float] NULL,
[City] [varchar](100) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Insert Records:

INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary], [City]) VALUES (1, N'Ahwin Roy', N'Software Engineer', 800000, N'Varanasi')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary], [City]) VALUES (2, N'Ritesh Shah', N'Team Leader', 900000, N'New Delhi')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary], [City]) VALUES (3, N'Vikas Gupta', N'DBA', 1200000, N'Allahabad')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary], [City]) VALUES (4, N'Akash Tripathi', N'Tester', 700000, N'Mumbai')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary], [City]) VALUES (5, N'Nitin Pandey', N'Web Developer', 8000000, N'Pune')
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO


Example 1:

Example 1:

The Following query will all Employees with an EmployeeName starting with “v”.

SELECT * FROM [dbo].[Employee] WHERE [EmployeeName] LIKE 'v%'

Example 2:

The Following query will all Employees with an EmployeeName ending with “a”.

SELECT * FROM [dbo].[Employee] WHERE [EmployeeName] LIKE '%y'

Example 3:

The following query will all Employees whose EmployeeName contains “in” at any position

SELECT * FROM [dbo].[Employee] WHERE [EmployeeName] LIKE '%in%'

Example 4:

The following query will all Employees with a City of length 4 ended with “e”

SELECT * FROM [dbo].[Employee] WHERE [EmployeeName] LIKE '___e'

Example 5:

The following query will all Employees with a City of length 6 with “M” at its first and “M” at its third position

SELECT * FROM [dbo].[Employee] WHERE [City] LIKE 'M_m___'

View More:

Conclusion:

I hope you understand how to use Like Operator in SQL Server. I would like to have your feedback, comments and suggestions.

Thank You.

149 Comments