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

This question already has answers here: SQL Server Escape an Underscore (8 answers) Closed 8 years ago.

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.

---

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.

---

use brakets [_]

This works for me in SQL Server 2005

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

---

Try it without the brackets:

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