7

I need to escape [ in an sql query for SQL Server

select * from sometable where name like '[something]';

I actually am looking for a [ before something and I don't want it to act like a wildcard. I tried :

select * from sometable where name like ''[something]';

But get error message from this:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'something'. Msg 105, Level 15, State 1, Line 1 Unclosed quotation mark after the character string ';

Andrei Petrenko
  • 3,922
  • 3
  • 31
  • 53
user840930
  • 5,214
  • 21
  • 65
  • 94
  • 2
    possible duplicate of [How can I escape square brackets in a LIKE clause?](http://stackoverflow.com/questions/439495/how-can-i-escape-square-brackets-in-a-like-clause) – onedaywhen Jan 24 '12 at 09:35

3 Answers3

7

Use:

select * from sometable where name like '[[]something[]]';

you may use as well:

select * from sometable where name like '\[something\]' escape '\';

Described in LIKE (Transact-SQL) on MSDN.

Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
4

Embed the [ in []

declare @T table
(
  name varchar(20)
)

insert into @T values
('abc'),
('[abc')

select *
from @T 
where name like '[[]a%'

Result:

name
--------------------
[abc

Have a look here at what you can do in the like expression. LIKE (Transact-SQL)

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • 1
    It might help to point out that it's using regular expressions, so the [...] denote an acceptable character set. And that's how/why this solution works. – weston Jan 24 '12 at 09:36
  • 1
    @weston - Updated the answer with a link to where you can read all about it. – Mikael Eriksson Jan 24 '12 at 09:42
0

Here's a little sample code. You need to embed the [ within []:

SELECT FirstName 
  FROM (SELECT '[Test]' AS FirstName) as t 
 WHERE FirstName LIKE '[[]%'
Neil Knight
  • 47,437
  • 25
  • 129
  • 188