How can I include strings which contain special characters in my WHERE clause?

Question

I am using SQL Server 2014 and I am faced with the following problem in the WHERE clause of my T-SQL query.

My WHERE clause will list the names of towns. However some of the names contain characters like an apostrophe in them. Example: ST JULIEN D'HOTMAN

My T-SQL looks as follows:

SELECT
...

FROM [TownList]

WHERE [townName] IN

(
...,

...,

'ST JULIEN D'HOTMAN',

...,

)

The above does not work because one or more of the [townName] contains an apostrophe.

From the solution given here (How to insert a value that contains an apostrophe (single quote)?, I need to re-write that value as 'ST JULIEN D''HOTMAN'

This is fine if you have a couple of Names that follow this principle. You can manually correct the way the values need to written. However, more than 200 to 300 names in my list have these issues.

Is there an alternative solution to approaching this problem?

Why *can't* you escape the characters? That *is* the solution. — Thom A, Apr 10 '22 at 10:38
"Escaping" is a standard coding terminology. For a single quote (`''`) in T-SQL, you would use 2 single quotes (`''`). In JSON, if you want to put a double quote (`"`) in, you would put a backslash (`\ `) first: `\"`. Why can't you escape the characters? — Thom A, Apr 11 '22 at 18:20

score 0 · Accepted Answer · answered Apr 10 '22 at 08:02

I would use a table or temp table to carry with your data before filtering the data

if you don't want to modify each [townName] there is another way you can try to SET QUOTED_IDENTIFIER OFF then use double quotation be the string Identify.

Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

SET QUOTED_IDENTIFIER OFF;

SELECT
...

FROM [TownList]

WHERE [townName] IN

(
...,

...,

"ST JULIEN D'HOTMAN",

...,

)

SET QUOTED_IDENTIFIER ON;

How can I include strings which contain special characters in my WHERE clause?

1 Answers1