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