How can I find square brackets in SQL by LIKE expressions?

Question

I need to find by sequence which contains symbols [] like !@#$%^&[]{}<>?/,.*(^ with a like expression in SQL Server 2014.

How I can escape brackets to find records which contain the target string?

Example

SELECT *
FROM [TARGET_TABLE]
WHERE [TARGET COLUMN] LIKE '%"!@#$^&[]{}<>?/,.*(^"%';

[TARGET TABLE] has a row with [TARGET COLUMN] which value is

some text "!@#$^&[]{}<>?/,.*(^"

but the result of the expression is empty.

I know [] is a wildcard, but how can I escape brackets to find strings with them?

Answer 1

LIKE has an escape character, which, by default is \. You can set it to something else. So this should work:

SELECT * 
FROM [TARGET_TABLE] 
WHERE [TARGET COLUMN] LIKE '%"!@#$^\[\]{}<>?/,.*(^"%';

You can set it to another character, but it is hard to choose one given your characters:

SELECT * 
FROM [TARGET_TABLE] 
WHERE [TARGET COLUMN] LIKE '%"!@#$^+[+]{}<>?/,.*(^"%' ESCAPE '+'

But, if you want to be more general, perhaps you want ^ for a character set. For instance to get a column that contains any non-alphnumeric character:

SELECT * 
FROM [TARGET_TABLE] 
WHERE [TARGET COLUMN] LIKE '%[^a-zA-Z0-9]%';

Or, if you want to find columns that only contain non-alphanumeric characters:

SELECT * 
FROM [TARGET_TABLE] 
WHERE [TARGET COLUMN] NOT LIKE '%[a-zA-Z0-9]%';

This idea can, of course, be extended to other sets of characters.

Answer 2

You can put the left bracket inside a class as [[]. When a closing bracket is not closing a class it is treated as a literal. (I'm using the word class as terminology from regexes where I think the SQL Server documentation just refers to this as a set.)

LIKE '%"!@#$^&[[]]{}<>?/,.*(^"%';

Answer 3

I have used this:

Create Table #Rohit (VarcharValues varchar(255))
Insert into #Rohit values
('"!@#$^&[]{}<>?/,.*(^"')

Then this:

SELECT * FROM [#Rohit] WHERE VarcharValues LIKE '%"!@#$^&\[]%' Escape '\'