title: T-SQL special characters to escape for LIKE operator wildcard search
body: <p>SQL Server has the LIKE operator to handle wildcard searches.  My customer wants to use the "*" (asterisk) character in the user interface of an application as the wildcard character.  I'm just wondering if there are any standard characters that I need to worry about (that are used as special characters in SQL Server) besides the "%" (percent) character itself before performing a LIKE wilcard search in case their keyword contains a "%" and needs to find a "%" in the actual string.  If so, what are they?</p>
  
  <p><strong>So please assume that [table1].[column1] will never have a "*" (asterisk) in the text string!</strong></p>
  
  <p>Here's what I have so far.  Do I need to handle more situations other than the standard <code>"%"</code> character and the custom <code>"*"</code> </p>
  
  <pre><code>-- custom replacement
  select REPLACE('xxx*xxx', '*', '%')
  
  -- standard replacements
  select REPLACE('xxx%xxx', '%', '[%]')
  select REPLACE('xxx_xxx', '_', '[_]')  -- ???
  select REPLACE('xxx[xxx', '[', '[[]')  -- ???
  select REPLACE('xxx]xxx', ']', '[]]')  -- ???
  </code></pre>
  
  <p><strong>Example:</strong></p>
  
  <pre><code>SET @p = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@p, ']', '[]]'), '[', '[[]'), '_', '[_]'), '%', '[%]'), '*', '%')
  
  SELECT 'xxxxxxxxx%xxxxxx' LIKE @p
  
  SELECT [table1].[column1] LIKE @p
  </code></pre>