TSQL Query: Escaping Special Characters

Question

Hello all,

I am trying to escape special characters in a TSQL query. I have done this before:

SELECT columns FROM table WHERE column LIKE '%\%%' ESCAPE '\'

And it has worked. Now I have tried to do this now:

UPDATE match SET rule_name='31' ESCAPE '\'

But it has failed. I know none of the vlaues have a \ but it should still work. I am guessing its because it needs a LIKE statement but how else can I escape characters that I am adding to a database?

In addition, does anyone have a link to all the special characters that should be escaped, I couldn't find any documentation on this!

Thanks all for any help

Answer 1

The documentation for the LIKE clause has a list of characters that require escaping.

ESCAPE is only defined as part of the LIKE clause, which is why your second query fails (no LIKE clause).

Answer 2

The point of the ESCAPE clause on the LIKE statement is to allow you to treat special characters like '%' as literals. When your updating there are no special characters that need to be escaped like that.

Edit

I misunderstood and thought the OP wanted to escape the like special characters. To escape the ' you enter ''.