sql server: need to escape [?

Question

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 ';

Answer 1

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)

Answer 2

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

SELECT FirstName 
  FROM (SELECT '[Test]' AS FirstName) as t 
 WHERE FirstName LIKE '[[]%'

Answer 3

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.