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.
Printable View
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?
Hi wait dear....its these weird encoders you know...they hit keysQuote:
Originally Posted by waitecj
that dont exist :D
thanks bazza....that code is what i needed...so educational :)Quote:
Originally Posted by bazza
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