t-sql LIKE and special characters

Question

"[" is not classed a unicode character http://en.wikipedia.org/wiki/List%5Fof%5FUnicode%5Fcharacters (my guess) as to why this wouldn't work:

declare @v nvarchar(255)
set @v =  '[x]825' 
select 1 
where  @v like  '[x]825'

Ta!

Answer 1

[] defines a range of characters for a pattern match. It has special meaning in a LIKE statement. Here's the documentation for it.

If you're looking for those characters explicitly, you'll need to escape them, like this:

declare @v nvarchar(255)
set @v =  '[x]825' 
select 1 
where  @v LIKE '![x]825' 
       ESCAPE '!'

Answer 2

[x] has a specific meaning to SQL server. The brackets are used for very basic regular expressions. SO what you are searching for is where the first character contains the letter X and of course that isn't the first character in your variable.

It is best not use like unless you intend to havea awildcard and it is a bad practice to have a wildcard be the first character as it makes the query use a table scan instead of an index.