Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I need to get results where the column contains a string that includes [ ]. When I use LIKE, it takes the [] as a wildcard sequence, and doesn't work as I need.

For example, I want to search through a column called Address and get any values where the string contains [Sydney]:

SELECT * FROM Location
WHERE Address LIKE '%[Sydney]%'

I can't use CONTAINS(), because this is not a full-text indexed column.

share|improve this question
add comment

1 Answer 1

up vote 3 down vote accepted

One option is to use ESCAPE:

DECLARE @x TABLE(s VARCHAR(32)); INSERT @x SELECT 'Hi [Sydney]!'

SELECT s FROM @x WHERE s LIKE '%\[Sydney\]%' ESCAPE '\';

Of course, you'll want to choose a character that is unlikely to appear in the data naturally, or this could mess up other searches.

Another option is to just escape the first character by including it in its own escape sequence, e.g. in this case the end ] is not considered a part of any wildcard sequence because the first one is eliminated:

DECLARE @x TABLE(s VARCHAR(32)); INSERT @x SELECT 'Hi [Sydney]!'

SELECT s FROM @x WHERE s LIKE '%[[]Sydney]%';

One more option is to not use patterns that support wildcards or wildcard sequences. For example, CHARINDEX:

DECLARE @x TABLE(s VARCHAR(32)); INSERT @x SELECT 'Hi [Sydney]!'

SELECT s FROM @x WHERE CHARINDEX('[Sydney]', s) > 0;
share|improve this answer
    
Thanks. I used the second option and it worked well. –  Sky Nov 27 '13 at 3:45
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.