need to clean up corrupted varchar2 field

[bookouri]

I have several hundred records in a table that appear to have been converted years ago from some previous database. I just happened to be doing some reporting on these old records and found that they have embedded in the "notes" column a chr(236)chr(10) so when i do a report that contains these records my report format is screwed up. I can select these records with:

select notes from mytable where notes like '%'||chr(236)||chr(10)||'%'

the question now is, how can i strip these chr(236) and chr(10) out of my notes column?

any suggestions would be appreciated..

 

[SantaMufasa]

I infer from your original post that the characters you wish to eliminate are always adjacent. Also, since those characters could be between words and their simple elimination might cause the running together of the word to the left with the word to the right, I propose that we replace those two characters with a single blank space. Here, then is the code that I recommend:

UPDATE <table_name>
  SET notes = replace(notes,chr(236)||chr(10),' ')
WHERE notes like '%'||chr(236)||chr(10)||'%';

Let us know if this satisfactorily resolves your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[bookouri]

you're absolutely correct...

thanks

 

[Beilstwh]

Nice one Dave, the replace was simple, but realizing that you should replace it with a space instead of just killing them was a nice catch.

Bill
Oracle DBA/Developer
New York State, USA

 

[SantaMufasa]

Very thoughtful of you to feel that way.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]