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.
