Re: Control Characters in column

From: andy <andyclifton_at_yahoo.co.uk>
Date: 27 Feb 2004 02:00:31 -0800
Message-ID: <475aa3f5.0402270200.1b2a2315_at_posting.google.com>


"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<NuOdnSnBD_OvaqDd4p2dnA_at_comcast.com>...
> "andy" <andyclifton_at_yahoo.co.uk> wrote in message
> news:475aa3f5.0402260528.446057f8_at_posting.google.com...
> | Greetings all.
> | Problem: Have control characters mostly ^B which are loaded into a
> | table in our database forming part or a narative field. In TOAD these
> | appear as a thick bar - kind of like a pipe character '|' but thicker.
> | We need to get rid of these as they are causing problems with our
> | information exchange software. Does anybody have any idea how to do a
> | search and replace for such characters in an oracle 9i database? The
> | table has over 60M record so I dont facy checking it manually as I
> | recon Ill be 84 years old by the time im finished!
> | Thanks.
>
> use the replace and the chr functions -- if i got the chr code right, this
> should work, as long as you've got a sufficiently large rollback segment:
>
> update the60mtable
> set thebadcolumn = replace(thebadcolumn,chr(2),null)
> where thebadcolumn like '%'||chr(2)||'%'
>
> ;-{ mcs

Thanks Mark,
Never thought to cat the %'s to the chr function...Ill away and upset the DBAs now! Received on Fri Feb 27 2004 - 11:00:31 CET

Original text of this message