Re: Control Characters in column

"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 Received on Thu Feb 26 2004 - 14:40:00 CET