ESCAPE Special Characters in TSQL

At times, we might need to search a string in TSQL using LIKE operator for a special character (%,_,[,]). These special characters do have different meaning in normal string comparisons so we need to escape them for making them to be a part of search pattern.  We can use either ESCAPE keyword followed by the character we want to escape or we can directly use ‘[]’ to escape special characters in SQL Server.

Please find below sample code for escaping special characters –

/**************************** Create TestDB Database *****************************/ 

IF  NOT EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       CREATE DATABASE [TestDB];
END
GO

USE [TestDB]
GO


/**************************** Create Employee Table ******************************/

IF EXISTS(SELECT * FROM sys.sysobjects WHERE type = 'U' AND name = 'Employee')
BEGIN
    DROP TABLE [dbo].[Employee];
END
GO

CREATE TABLE [dbo].[Employee](
    [EmpId] [int] NOT NULL,
    [EmpName] [nvarchar](32) NOT NULL
)
GO


/*********************** Enter duplicate Employee Details ************************/

INSERT INTO [dbo].[Employee] ([EmpId],[EmpName]) VALUES (1,'Rami');
INSERT INTO [dbo].[Employee] ([EmpId],[EmpName]) VALUES (1,'%Rami');
INSERT INTO [dbo].[Employee] ([EmpId],[EmpName]) VALUES (1,'Ra[mi');
INSERT INTO [dbo].[Employee] ([EmpId],[EmpName]) VALUES (1,'_Rami');
GO 

-- Both are same, Selects only records which are starting with %
SELECT [EmpId],[EmpName] FROM [dbo].[Employee] WHERE [EmpName] LIKE '\%R%' ESCAPE '\';
SELECT [EmpId],[EmpName] FROM [dbo].[Employee] WHERE [EmpName] LIKE '[%]R%';

-- Both are same, Selects only records which are starting with _
SELECT [EmpId],[EmpName] FROM [dbo].[Employee] WHERE [EmpName] LIKE '\_R%' ESCAPE '\';
SELECT [EmpId],[EmpName] FROM [dbo].[Employee] WHERE [EmpName] LIKE '[_]R%';

-- Both are same, Selects only records which consists only [
SELECT [EmpId],[EmpName] FROM [dbo].[Employee] WHERE [EmpName] LIKE '%\[%' ESCAPE '\';
SELECT [EmpId],[EmpName] FROM [dbo].[Employee] WHERE [EmpName] LIKE '%[[]%';
GO


/***************************** Drop TestDB Database ******************************/

USE master
GO

IF EXISTS (SELECT [name] FROM master.[dbo].[sysdatabases] WHERE [name] = 'TestDB')
BEGIN
       DROP DATABASE [TestDB];
END
GO

Output –