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.

score 46 · Accepted Answer · answered Feb 19 '10 at 23:27

46

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.

answered Feb 19 '10 at 23:27

Mark Byers

1

Thanks, this worked. I am not sure why it wasn't working before but it does now so much appreciated. – James Thomas Feb 19 '10 at 23:40

score 16 · Answer 2 · answered Feb 19 '10 at 23:26

16

use brakets [_]

This works for me in SQL Server 2005

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

answered Feb 19 '10 at 23:26

Jose Chama

+1: Supporting link: http://sqlserver2000.databases.aspfaq.com/how-do-i-search-for-special-characters-e-g-in-sql-server.html – OMG Ponies Feb 20 '10 at 00:04

score 5 · Answer 3 · answered Feb 19 '10 at 23:28

5

Try it without the brackets:

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

answered Feb 19 '10 at 23:28

Guffa

3 Answers3