Control Characters in column

[andy]

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.

[Mark C. Stock]

"andy" <an*********@yahoo.co.uk> wrote in message
news:47**************************@posting.google.c om...
| 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

[andy]

"Mark C. Stock" <mcstockX@Xenquery .com> wrote in message news:<Nu********************@comcast.com>...

"andy" <an*********@yahoo.co.uk> wrote in message
news:47**************************@posting.google.c om...
| 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!