CODEWITHSUNDEEP
regexoracle-databasereplaceplsqloracle10g
user1633800
user1633800

Reputation: 331

how to escape special character in a string in oracle(pl/sql)

I have no programming experience in oracle DB (Pl/sql) I need to write a function where I need to escape all these characters " (double quotes) : (colon) \ (backslash) in a given string.

I think I can use replace function to replace all these characters " with \", \ with \\, : with \: but I don't know an efficient way to go about it.

Upvotes: 2

Views: 5706

Answers (1)

Politank-Z
Politank-Z

Reputation: 3721

You could use REGEXP_REPLACE:

SELECT REGEXP_REPLACE ( '\' || ':"', '([\:"])', '\\\1', 1, 0) from dual;

Returns:

\\\:\"

The first parameter '\' || ':"' is a stand in for your input string to be escaped. The second parameter '([\:"])' is a regular expression matching your characters to be escaped. The third parameter '\\\1' is the replacement string: a backslash and the contents of the capture group. The fourth parameter 1 is the starting point in the string to begin replacing; Oracle counts the first character in a string as number one. The fifth parameter is which occurrence of your pattern to replace; 0 means replace them all.

Upvotes: 3

Related Questions