-
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.
Behind The Success And Failure Of A Man Is A Woman
-
How did it get there in the first place? Did you change charactersets for something?
Assistance is Futile...
-
-
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
Behind The Success And Failure Of A Man Is A Woman
-
Originally Posted by bazza
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
Last edited by yxez; 10-11-2006 at 08:24 PM.
Behind The Success And Failure Of A Man Is A Woman
-
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
Behind The Success And Failure Of A Man Is A Woman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|