title: Why is there an ESCAPE argument of the LIKE operator?
body: <p>If you check out the definition of the <a href="http://msdn.microsoft.com/en-us/library/ms179859.aspx" rel="nofollow">LIKE</a> operator, you will see that there is an optional argument named <code>ESCAPE</code> which is described as follows:</p>
  
  <blockquote>
    <p>Is a character that is put in front of a wildcard character to
    indicate that the wildcard should be interpreted as a regular
    character and not as a wildcard. escape_character is a character
    expression that has no default and must evaluate to only one
    character. </p>
  </blockquote>
  
  <p>So when you write a <code>WHERE</code> clause like given below, you tell the SQL Server engine to treat the exclamation mark as an escaping character (just like the <code>'\'</code> character in C#) to list comments that include '30%' substring:</p>
  
  <pre><code>WHERE comment LIKE '%30!%%' ESCAPE '!'
  </code></pre>
  
  <p>Now here it gets confusing for me. It is already possible to escape wildcard characters by putting them inside square brackets. Why would one want to introduce the <code>ESCAPE</code> argument? It is there for a reason I guess.</p>
  
  <p><strong>EDIT:</strong> I see several answers that explain how <code>ESCAPE</code> works. I know that <code>ESCAPE</code> is used to escape wildcard characters but what I also know is that you can escape those wildcard characters using square brackets. I just want to understand what "ESCAPE method" has and "square brackets method" doesn't.</p>
  
  <p><strong>EDIT 2:</strong> Although Szymon's example is totally valid, I don't feel this is the only reason ESCAPE was introduced. I might be wrong of course but I suspect there might be some performance based reasons, I don't know for sure. Szymon, You can achieve the same like follows: </p>
  
  <pre><code>DECLARE @TEST_STRING VARCHAR(100) = 'ddd]eee';
  SELECT 'MATCHED! (Szymon)' WHERE @TEST_STRING LIKE '%[abc!]]%' ESCAPE '!';
  SELECT 'MATCHED! (Altern)' WHERE @TEST_STRING LIKE '%[abc]%' OR @TEST_STRING LIKE '%]%';
  </code></pre>
  
  <p>As a side note, you don't need to escape the closing square bracket character.</p>