0

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 contains 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 result of expression is empty.

I know what [] is wildcard, but don't know how escape brackets to find strings with them.

marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
TAB
  • 11
  • 1
  • 2
  • I don't know the exact details of sql-server implementation, but `'%\[\]%'` would do the job (this will be the case in Oracle and MySQL). – FDavidov Aug 13 '16 at 10:53

3 Answers3

2

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.

Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
1

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 '%"!@#$^&[[]]{}<>?/,.*(^"%';
shawnt00
  • 14,501
  • 3
  • 15
  • 22
0

I have Used this

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

Then This

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