CODEWITHSUNDEEP
oracle-databaseplsqloracle10gescaping
James213
James213

Reputation: 977

Escape special characters PL/SQL without using REPLACE

I'm trying to escape and special characters found in a string. Currently I'm using the replace function searching for any special character individually and adding \ in front of it. as shown below.

directory := replace(directory, ' ', '\ ');
directory := replace(directory, '(', '\(');
directory := replace(directory, ')', '\)');
directory := replace(directory, '''', ''\'');
etc

What I am wondering is if there is a cleaner way to execute this action?

Any help or suggestions are greatly appreciated.

Upvotes: 0

Views: 6135

Answers (2)

the_slk
the_slk

Reputation: 2182

What to replace:

SELECT  REGEXP_REPLACE(str, '\w') AS output
FROM
(
        SELECT  'perl -pe ''$_ .= "\n" unless /^$/''' AS str
        FROM    DUAL
);

Result:

 - '$ .= "\"  /^$/'

When it is erased:

SELECT  REGEXP_REPLACE(str, '\W') AS output
FROM
(
        SELECT  'perl -pe ''$_ .= "\n" unless /^$/''' AS str
        FROM    DUAL
);

Result:

perlpe_nunless

Upvotes: 0

Przemyslaw Kruglej
Przemyslaw Kruglej

Reputation: 8113

Try this:

SELECT
  regexp_replace('my ''string'' with special (characters)', '([() ''])', '\\\1')
FROM
  dual;

Output:

my\ \'string\'\ with\ special\ \(characters\)

Upvotes: 3

Related Questions