T-SQL: LIKE Operator and Wildcard Characters

An interesting issue I came into recently. Suppose you have a search procedure - which consists of some SELECT query which gets a query phrase as a parameter and searches through a table column using LIKE.

The most common pattern is to add % at the beginning and the end of the search parameter so the query looks like so:

WHERE Col LIKE '%' + @param + '%'

Or you may perform concatenation before running the query as well :) However need to take into account the following. The search parameter which usually comes down to the procedure from the application user interface can contain T-SQL reserved wildcard characters such as => % [ ] _ etc. Such input will cause the query to return wrong results.

Therefore it is useful to remember to filter out or replace these characters so MSSQL will consider these characters literals. For example if we have rectangular braces => [], need to replace the opening brace => [ with the sequence => [[] before running the query.

Related Posts:

• MSSQL: Search and Replace in a TEXT(NTEXT) column
• T-SQL: Get Age from Date of Birth
• MSSQL 2005 Tip: Using TOP with Subquery
• T-SQL Query Processing Order
• How to Insert DBCC Output into MSSQL Table
• MSSQL: sp_xml_preparedocument is not supported in fibers mode
• MSSQL: Got arithmetic overflow for INT ?
• MSSQL 2005 Express Edition - unbeatable
• T-SQL: The Rozenshtein Method
• Always Use Stored Procedures in MSSQL