Replace Quote With ASCII Value (and back again)

[jtanner]

Hello,

In an Oracle 10g R2 database in a VARCHAR2(2000) comments field I need to replace all single quotes with their ASCII values before INSERT\UPDATE and also replace it back again when displaying.

Can someone please provide a PL/SQL code snippet to achieve this?

This one has me stumped, thanks for your advice.

JT

 

[SantaMufasa]

JT,

Here is code that does, I believe, what you want:

select * from tanner;

COMMENTS
-----------------------------
'This is JTANNER'S text.'
'This is SANTAMUFASA'S text.'

update tanner set comments = replace(comments,'''','chr(39)');

2 rows updated.

select * from tanner;

COMMENTS
-----------------------------------------------
chr(39)This is JTANNERchr(39)S text.chr(39)
chr(39)This is SANTAMUFASAchr(39)S text.chr(39)

2 rows selected.

select replace(comments,'chr(39)','''') comments from tanner;

COMMENTS
-----------------------------
'This is JTANNER'S text.'
'This is SANTAMUFASA'S text.'

2 rows selected.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]