garbage in my table

Hi Friends,

I have to clean up garbage in my EMP table under column NAME.
It has unprintable chars in like :

NAME
-----










?

E
P
?
?
A
A
?



How can i update this unprintable chars to null please.....

Thanks a lot.

How did it get there in the first place? Did you change charactersets for something?

see:

http://asktom.oracle.com/pls/ask/f?p...A:595423463035

Quote:

---

Originally Posted by waitecj

How did it get there in the first place? Did you change charactersets for something?

---

Hi wait dear....its these weird encoders you know...they hit keys
that dont exist :D

Quote:

---

Originally Posted by bazza

see:

http://asktom.oracle.com/pls/ask/f?p...A:595423463035

---

thanks bazza....that code is what i needed...so educational :)
by the way, "bazza" in our dialect its "wet".

I just got confused....

I did:

select name from emp where name like '%?%' or name like '%%';

but no rows selected :confused:

hi again dear friends.....

I made these codes which selects rows/columns with bad or
unprintable characters.

------------
code 1 (sqlplus):

select ENAME from EMP where
length(translate(trim(upper(ENAME)),'.'||'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890','.'))>0;
------------

code 2 (function):

## This function checks column which has bad chars.
## sql> select col1 from tab1 where strip_char(col1)=1;

CREATE OR REPLACE FUNCTION strip_char (p_string VARCHAR2)
RETURN integer
AS
v_len integer;
BEGIN
v_len := length(translate(trim(upper(p_string)),'.'||'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890','.'));
return v_len;
EXCEPTION
WHEN OTHERS THEN RETURN 1;
END;
/
-----------------------

The 2 code works fine....but code 1 is way faster than code 2
5 secs agains 35 with EMP having 4 million rows.

Can u tune/rewrite code2 (function) to run faster pls....

Thanks