TSQL syntax for enclosing search terms within brackets

Question

I believe [ and ] are special characters when using with LIKE clause in TSQL( SQlserver 2005 if it matters). How do i escape the search term in the stored procedure, i did below but does not return valid records even while exists

SELECT * FROM Table WHERE Column LIKE '%['+ @searchedTerm +']'

So which is the valid thing to do, when searching like above??

Answer 1

You need to escape the opening (not the closing) bracket like this:

LIKE '%[[]' + @searchedTerm + ']'

The MSDN page for LIKE has a section "Using Wildcard Characters As Literals"

Edit, after comment

• LIKE '[[]cap]%' searches for a name containing the string [cap]
• LIKE '[wel]%' searches for a name containing one of the letters w, e or l