How do I escape _ in SQL Server? [duplicate]

Question

I am trying to escape the underscore character in a LIKE Statement. I have tried to use the ESCAPE keyword as follows:

COLUMNNAME NOT LIKE '%[\_]xyz%' ESCAPE '\' 

but it doesn't work. It is still filtering out %xyz% when I really want it to filter out %_xyz%.

If not by the ESCAPE keyword, how else can this be accomplished?

Any help is appreciated.

Answer 1

Just this should work:

COLUMNNAME NOT LIKE '%[_]xyz%' 

You don't need the ESCAPE here. What you wrote should also work.

If you do want to use ESCAPE you could do this:

columnname NOT LIKE '%\_xyz%' ESCAPE '\'; 

Documentation on escape characters is here.