How to escape characters in Oracle SQL

When you have to query an Oracle table and want to find rows containing a certain string, you can use wildcards in your WHERE clause. But what do you do if you need to search for rows containing a wildcard as a string? This question came up at one of our customers.

In a query searching for a string inside a column, you will use something similar to:

SELECT column
  FROM table
 WHERE column like '%string%';

But what do you do when you want to find a string containing a %-sign? Because this is the wildcard character, you will get all rows as a result from your query.
 

Luckily there is a solution for this problem. The ESCAPE keyword.

By using this keyword you can escape any character and search for strings containing wildcard characters.

Here is an example:

SELECT column
  FROM table
 WHERE column LIKE '%\%%' ESCAPE '\';

This way it will not see the 2nd %-character as a wildcard, but as a normal string character.