0

I'm trying to remove a square box char from a column that is being used to populate xml's The problem is the xmls aren't being created correctly, and are creating a lot of work for me. so as a scripted job each dayb I'm going to try to run sql to update the values to remove the offending char.

so far I have 

select * from (select dump(narration) as dn,narration from ar_journal)
where dn like '%27%'
and dn not like '%=27:%';

This finds the offending stuff, but I'm wondering if it'd be possible to call the dn using a replace function?

note the dn is a dump of the column narration.

results are like the below DN Typ=1 Len=200: 96,27,97,110,115,102,101,114,32,102,114,111,109,32,67,117,115,116,111,109,101,114,32,32,32,32,32,32,32,32,32,57,48,48,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32

Narration CharWasHere'ransfer from Customer 900
The values are unpredictable but the char is always the same,

any help would be greatly appreciated.

Cheers,

Ben

Ben Coughlan
  • 555
  • 3
  • 12
  • 23

1 Answers1

0
select replace(narration, chr(27)) from table
Egor Skriptunoff
  • 23,359
  • 2
  • 34
  • 64
  • Thanks a million Egor, do you know of a way that I could select a number of these chars at once? i.e. if chr(27), chr(28), chr(60), chr(62) were all causing errors? – Ben Coughlan Apr 15 '13 at 09:11
  • @BenCoughlan - `regexp_replace(replace(narration, chr(0)),'['||chr(1)||'-'||chr(31)||'<>]')` – Egor Skriptunoff Apr 15 '13 at 09:32