garbage in my table

[yxez]

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.

[waitecj]

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

[bazza]

see:

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

[yxez]

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

[yxez]

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

[yxez]

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