SQL SERVER – Remove special characters when writing SQL queries using escape

Remove special characters when writing SQL queries using escape?

 

Escape quotes

 

User can escape single quote using two single quotes (NOT double quote).

Following T-SQL Script will give error

 

USE AdventureWorks
GO
SELECT *
FROM Person.Address
WHERE City = 'Villeneuve-d'Ascq'

Resultset:

Error: 105 Unclosed quotation mark after the character string ‘

To fix the error mentioned above, escape single quotes with two single quotes as displayed in following example.

USE AdventureWorks
GO
SELECT *
FROM Person.Address
WHERE City = 'Villeneuve-d''Ascq'

 

Escape wildcard characters

 

The LIKE keyword allows for string searches. The '_' wild card character is used to match exactly one character, while '%' is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Examples:

 

SELECT name FROM emp

   WHERE id LIKE '%/_%' ESCAPE '/'

 

SELECT name FROM emp

   WHERE id LIKE '%\%%' ESCAPE '/'

 

For ex, If you don't want to show all the records by passing ‘%' as input parameter (If user passed and you have like parameter), then you can use escape functionality.

SELECT name FROM emp

   WHERE id LIKE '%%' ESCAPE '%'

 

 

Thanks J

Ebook Download
View all
Learn
View all