CODEWITHSUNDEEP
sql-serveroracle-databasesql-like
Sulejmani
Sulejmani

Reputation: 11

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.

Upvotes: 0

Views: 2427

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272106

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 '\'

Upvotes: 1

kirchner
kirchner

Reputation: 974

You'll need something like the first answer above, but you don't need to use a \ as the escape. You can choose whatever you want using the ESCAPE clause.

But if:

  • users are allowed to enter wildcards;
  • and you need to use LIKE;
  • and you don't want them treated like wildcards;

then you have to escape them somehow.

Perhaps you can reserve some char you know the user will not need and make that the escape char.

As far as I can tell in Oracle you only need to escape the percent (%) and the underbar (_). In SQL Server you also have to consider brackets.

A good thing is that overescaping does not look like it will cause problems, so even though you don't need to espace brackets in Oracle, doing so is ok.

Upvotes: 0

Andrey Hideki Nakano
Andrey Hideki Nakano

Reputation: 41

The ESCAPE clause identifies the backslash (\) as the escape character

SELECT *
FROM PRODUCTS
WHERE REDUCTION LIKE '50\%'

Upvotes: 0

Related Questions