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 –