4

Possible Duplicate:
SQL Server LIKE containing bracket characters

I am having a problem with pattern matching.I have created two objects say,with codes

1)[blah1]
2)[blah2] respectively

in the search tab,suppose if i give "[blah" as the pattern,its returning all the strings i.e., [blah1] and [blah2]

The query is

select *
from table1 
where code like N'%[blah%'

I guess the problem is with the condition and special characters. Please do revert if you have as solution. Is there any solution where we can escape the character"[". I tried to change the condition as N'%[[blah%'.But even then its returning all the objects that is in the table.

Community
  • 1
  • 1
Sushma Prakash Iyengar
  • 51
  • 1
  • 5
  • 3
    The [documentation](http://msdn.microsoft.com/en-us/library/ms179859.aspx) for `LIKE` includes an example of how to escape a bracket (in the "Using Wildcard Characters As Literals" section), is it not clear? – Pondlife Nov 30 '12 at 16:54
  • @Pondlife Shhh, that documentation is top secret. – Kermit Nov 30 '12 at 16:58
  • 1
    @njk I guess so, since very few people seem to be able to find it :-) – Pondlife Nov 30 '12 at 17:06

3 Answers3

4

When you don't close the square bracket, the result is not specified.

However, the story is different when you close the bracket, i.e.

select *
from table1 
where code like N'%[blah%]%'

In this case, it becomes a match for (any) + any of ('b','l','a','h','%') + (any). For SQL Server, you can escape characters using the ESCAPE clause.

select * from table1 where code like N'%\[blah%\]%' escape '\'

SQL Fiddle with examples

RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
2

You can escape a literal bracket character this way:

select *
from table1 
where code like N'%[[]blah%'

Source: LIKE (Transact-SQL), under the section "Using Wildcard Characters As Literals."

I guess this is Microsoft's way of being consistent, since they use brackets to delimit table and column identifiers too. But the use of brackets is not standard SQL. For that matter, bracket as a metacharacter in LIKE patterns is not standard SQL either, so it's not necessary to escape it at all in other brands of database.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
-2

As per My understanding, the symbol '[', there is no effect in query. like if you query with symbol and without symbol it shows same result.

Either you can skip the unwanted character at UI Level.

      select * from table1 where code like '%[blah%' 
  select * from table1 where code like '%blah%'

Both shows same result.

Akshay Joy
  • 1,765
  • 1
  • 14
  • 23
  • 1
    Did you test this? Both queries do not return the same thing at all: `declare @t table (s varchar(10)); insert into @t select 'a[blah]b'; select * from @t where s like '%blah%'; select * from @t where s like '%[blah%';` – Pondlife Nov 30 '12 at 17:09