How to avoid a square bracket when using LIKE?

Question

Possible duplicate:
SQL Server LIKE containing parentheses

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

1)[blah1] 2)[blah2] respectively 

in the search tab, suppose that if I give "[blah" as a template, returning all the lines ie [blah1] and [blah2]

Request

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

I think the problem is with the condition and special characters. Please return if you have a solution. Is there any solution where we can escape the "[" character. I tried to change the condition as N '% [[blah%'. But even then, it returns all the objects that are in the table.

Answer 1

If you do not close the square bracket, the result is not indicated.

However, the story closes 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

Answer 2

You can avoid the letter character in this way:

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

Source: LIKE (Transact-SQL) in the section “Using Wildcards as Literals”.

I assume this is Microsoft's way of being consistent, as they use parentheses to also distinguish between table and column identifiers. But using parentheses is not standard SQL. In this case, the bracket as a metacharacter in LIKE templates is also not standard SQL, so there is no need to avoid it at all in other brands of the database.

Answer 3

According to My understanding, the symbol "[", the request has no effect. for example, if you request a character without a character, it shows the same result.

Or you can skip the unwanted character at the user interface level.

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

Both show the same result.