Removal or ACSII Code

Removal or ACSII Code

Post by Mike Grunt » Fri, 18 Dec 1998 04:00:00



Have a problem with users copy from Window apps and pasting into a
"note" field - VARCHAR2(2000).  The problem is that ASCII codes such as
line feeds, tabs, etc are also copied and stored.  When spooling to a
delimited text file or exporting data these ASCII codes (CODES 1-31
primarily) really can*things up.

Is there an ORA Utility or Update/Insert Trigger that I'm not aware of
that I can use?  If not any suggestions or sample code.

Thanks

Mike Gruntz

 
 
 

Removal or ACSII Code

Post by Andrew Protaso » Sat, 19 Dec 1998 04:00:00


Hi,

You can use translate function to remove special characters:

SVRMGR> select 'a'||chr(10)||'b' from dual;
'A'
---
a
b
1 row selected.
SVRMGR> select translate('a'||chr(10)||'b',chr(10),' ') from dual;
TRA
---
a b
1 row selected.

Andrew Protasov

>   Have a problem with users copy from Window apps and pasting into a
>   "note" field - VARCHAR2(2000).  The problem is that ASCII codes such as
>   line feeds, tabs, etc are also copied and stored.  When spooling to a
>   delimited text file or exporting data these ASCII codes (CODES 1-31
>   primarily) really can*things up.

>   Is there an ORA Utility or Update/Insert Trigger that I'm not aware of
>   that I can use?  If not any suggestions or sample code.

>   Thanks

>   Mike Gruntz



 
 
 

Removal or ACSII Code

Post by Arnold Schomme » Tue, 22 Dec 1998 04:00:00



> Have a problem with users copy from Window apps and pasting into a
> "note" field - VARCHAR2(2000).  The problem is that ASCII codes such as
> line feeds, tabs, etc are also copied and stored.  When spooling to a
> delimited text file or exporting data these ASCII codes (CODES 1-31
> primarily) really can*things up.

> Is there an ORA Utility or Update/Insert Trigger that I'm not aware of
> that I can use?  If not any suggestions or sample code.

> Thanks

> Mike Gruntz


The translate function should do. Looks horrible, but ...
Try something like
        SELECT TRANSLATE(note,
                        'x' || CHR(0) || CHR(1) || ... || CHR(31),
                        'x')
          FROM your_table;
(the strange 'x' is required: if any of the arguments was empty, the
result would be empty)

Hope it helps.

Arnold Schommer