How do I escape _ in SQL Server?

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.

user_61974 · Answer

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.

user_241637 · Answer

use brakets [_]

This works for me in SQL Server 2005

select *
from #table 
where a like '%[_]xyz%'

Guffa_69083 · Answer

Try it without the brackets:

COLUMNNAME NOT LIKE '%\_xyz%' ESCAPE '\'