How to avoid multiple characters in sql-like string?

Question

One character can be escaped as follows:

select * from tableName where columnName like 'CU_C' escape '_'; 

I need to avoid a few characters ("%" and "_"):

 select * from tableName where columnName like 'C%U_C' escape ??; 

How to avoid multiple characters?

Answer 1

You misunderstood the meaning of escape : it allows you to define a character in such a way that when you put it in front of another character, this other character is interpreted literally, and not as a metacharacter. You only need one such escape character: you can use it to escape the metacharacter.

In the example below, I used '#' as my escape character:

 select * from tableName where columnName like 'C#%U#_C' escape '#' 

This tries to match the strings C%U_C , where both '%' and '_' interpreted literally, and not as a sequence of any characters or any single character.