replace non characters. 2

[ptheriault]

I'm having a problem with a varchar record in my database. There is an invalid character that is causing replication to a sql server to fail. I need to update the records at the Oracle db to strip out the bad character.

Here is what the data looks like.(but between the I and l and the word Ill[b/] there is a box.

I?ll revise the audit.

Does anyone know what function I need to use here?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[KenCunningham]

Could you not just update the record to overwrite the 'bad' entry completely? Or have I misunderstood the question?

I want to be good, is that not enough?

 

[ptheriault]

Thanks Ken,
That's what I ended up doing to correct this one problem.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[Dagon]

If you know the ascii value of the character, you could do something like:

select translate('YZabaA', 'b'||chr(97), 'b') from dual

which in this example removes all the letter 'a' characters.

If you don't know the ASCII value, you can find it by doing:

select ascii(substr(text,2,1)) from table

 

[ptheriault]

Thanks Dagon,
That is what I was looking for in-case I run into this problem in the future will a larger amount of text.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[engineer2100]

Actually this issue is caused by an incorrect character set. I am confident on this because we recently faced this issue and changing the character set made the difference....

 

[TheKidd]

Hey Dagon,
Can you explain

select ascii(substr(text,2,1)) from table

It doesn't work for me.

 

[SantaMufasa]

Kidd,

The above code means, "Extract (substring) from a column named 'text', beginning at the second position for a length of 1 character, then display the ASCII code for that one character."

As an example:

select ascii(substr('abcdefg',2,1)) from dual;

ASCII(SUBSTR('ABCDEFG',2,1))
----------------------------
                          98

(The ASCII code for a lowercase "b" is 98. Here is the proof using the "reverse" of the ASCII function, "CHR"):

select chr(98) from dual;

CHR(98)
-------
b

Let us know if this answers your question.

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

 

[TheKidd]

Understood Mufasa, Thanks for clarifying.