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