Escape special characters for Oracle and SQL Server in the same query

I have following query:

SELECT *
FROM PRODUCTS
WHERE REDUCTION LIKE '50%'

I'm required to use the LIKE clause. This query needs to run on both Oracle and SQL Server.

Now there is an issue because I want to match all products with a reduction of 50%. But the data might contain a reduction of 50.50%. Because '%' is a special character it matches both of them.

I want to escape all special characters, like the % in my query so that I only get the products with 50% reduction.

Is there an uniform solution to escape special characters on a dynamical way for both Oracle and SQL server?

Using a backslash is not a solution, because we don't know in practice what the input will be.

Answer

The ESCAPE clause works in Oracle and SQL Server.

As for your input, you need to replace the all occurrences of % with \% (preferably before passing the value to RDBMs). You can do this inside a query as well since, fortunately, Oracle REPLACE and SQL Server REPLACE functions have similar signature:

CREATE TABLE tests(test VARCHAR(100));

INSERT INTO tests VALUES('%WINDIR%\SYSTEM32');
SELECT *
FROM tests
WHERE test LIKE REPLACE(REPLACE('%WINDIR%\SYSTEM32', '\', '\\'), '%', '\%') ESCAPE '\'