By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,502 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,502 IT Pros & Developers. It's quick & easy.

Control Characters in column

P: n/a
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.
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

"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
Jul 19 '05 #2

P: n/a
"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!
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.