garbage in my table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: garbage in my table

  1. #1
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    How did it get there in the first place? Did you change charactersets for something?
    Assistance is Futile...

  3. #3
    Join Date
    Jul 2002
    Posts
    335

  4. #4
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    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
    Behind The Success And Failure Of A Man Is A Woman

  5. #5
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    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 09:24 PM.
    Behind The Success And Failure Of A Man Is A Woman

  6. #6
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    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
  •  


Click Here to Expand Forum to Full Width