Re: Removal or ACSII Code

From: Arnold Schommer <aschommer_at_fs-edv.de>
Date: Mon, 21 Dec 1998 17:44:00 +0100
Message-ID: <367E7AD0.1E1A52A@fs-edv.de>

Mike Gruntz wrote:
>
> 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 screw 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
> mgruntz_at_ix.netcom.com

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 Received on Mon Dec 21 1998 - 10:44:00 CST