Sql server: Special Characters (%,[,]) in LIKE operator using ESCAPE keyword

  

Finding special characters such as percentage symbol on sql column would tricky. The below sql would not work to search '20%' on samplecolumn

SELECT samplecolumn FROM sampletable WHERE 
    samplecolumn LIKE '%20%%' 

Instead

SELECT samplecolumn FROM sampletable WHERE 
    samplecolumn LIKE '%20\%%' ESCAPE '\'

"ESCAPE" keyword are used within an SQL statement to tell the engine that the escaped part of the SQL string should be handled differently. In above SQL ESCAPE character is mentioned as '\' hence character after '\' is processed differently instead of normal there '%' character is search in "samplecolumn" column of "sampletable" table.

Likewise, underscore and any other special can be escaped from normal operation.

SELECT samplecolumn FROM sampletable WHERE 
    samplecolumn LIKE '%|_%%' ESCAPE '|'

In above sql '|' symbol is used as escape character.