Finding rows where a column contains [ and ] characters 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.
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) &gt; 0;