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