65

I am using SQL Server 2008. I have a table with the following column:

sampleData (nvarchar(max))

The value for this column in some of these rows are lists formatted as follows:

["value1","value2","value3"]

I'm trying to write a simple query that will return all rows with lists formatted like this, by just detecting the opening bracket.

SELECT * from sampleTable where sampleData like '[%'

The above query doesn't work, because '[' is a special character. How can I escape the bracket so my query does what I want?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
CJS
  • 1,455
  • 1
  • 13
  • 17

3 Answers3

95
 ... like '[[]%'

You use [ ] to surround a special character (or range).

See the section "Using Wildcard Characters As Literals" in SQL Server LIKE

Note: You don't need to escape the closing bracket...

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
gbn
  • 422,506
  • 82
  • 585
  • 676
37

Aside from gbn's answer, the other method is to use the ESCAPE option:

SELECT * from sampleTable where sampleData like '\[%' ESCAPE '\'

See the LIKE Transact-SQL documentation for details.

KyleMit
  • 30,350
  • 66
  • 462
  • 664
Ed Harper
  • 21,127
  • 4
  • 54
  • 80
3

Just a further note here...

If you want to include the bracket (or other specials) within a set of characters, you only have the option of using ESCAPE (since you are already using the brackets to indicate the set).

Also you must specify the ESCAPE clause, since there is no default escape character (it isn't backslash by default as I first thought, coming from a C background).

E.g., if I want to pull out rows where a column contains anything outside of a set of 'acceptable' characters, for the sake of argument let's say alphanumerics... we might start with this:

SELECT * FROM MyTest WHERE MyCol LIKE '%[^a-zA-Z0-9]%'

So we are returning anything that has any character not in the list (due to the leading caret ^ character).

If we then want to add special characters in this set of acceptable characters, we cannot nest the brackets, so we must use an escape character, like this...

SELECT * FROM MyTest WHERE MyCol LIKE '%[^a-zA-Z0-9\[\]]%' ESCAPE '\'

Preceding the brackets (individually) with a backslash and indicating that we are using backslash for the escape character allows us to escape them within the functioning brackets indicating the set of characters.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Keith Fearnley
  • 125
  • 6