DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: clean up data

  1. #1
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639

    clean up data

    Hi friends,

    My database contained garbage like the char '-' in the NAME column.
    When I tried to update it to null it can not be found or selected.

    UPDATE EMP SET NAME=NULL WHERE NAME='-';

    0 rows updated.


    Help me please how to handle this character.

    Thanks

  2. #2
    Join Date
    Sep 2000
    Location
    Chennai, India
    Posts
    865

    Re: clean up data

    Originally posted by kris123
    UPDATE EMP SET NAME=NULL WHERE NAME='-';
    Replace the above with a LIKE clause...

    Code:
    UPDATE EMP SET NAME=NULL WHERE NAME LIKE '%-%';
    BTW, do a SELECT and verify it is what you want to do and then do the UPDATE.

    HTH.

  3. #3
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Thanks friend,

    Can I use the wild card '%' in decode statement?
    You know I got lots of columns that contain the char '-'
    and wanted to change it to null. So instead of doing...

    update emp set field1=null where field1 like '%-%';
    update emp set field2=null where field2 like '%-%';
    update emp set field3=null where field3 like '%-%';
    .
    .
    .

    I tried

    update emp set f1=decode(f1,'%-%',null,f1),
    f2=decode(f2,'%-%',null,f2),
    f2=decode(f2,'%-%',null,f2),
    ...

    but it did not work, Is there other way to do this?


    Thanks

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by kris123
    I tried

    update emp set f1=decode(f1,'%-%',null,f1),
    f2=decode(f2,'%-%',null,f2),
    f2=decode(f2,'%-%',null,f2),
    ...

    but it did not work, Is there other way to do this?
    Use this one:
    Code:
    UPDATE emp SET f1 = DECODE(INSTR(f1,'-'),0,f1,NULL),
                   f2 = DECODE(INSTR(f2,'-'),0,f2,NULL),
                   f3 = DECODE(INSTR(f3,'-'),0,f3,NULL),
                   ...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Thanks Jurij.

  6. #6
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    Hi there helpful friends, need your help again.

    Can you help me how to validate a "DATE" column that contains invalid date? Say, I have an EMP table with column
    HIREDATE VARCHAR2(6), with mmddyy format. It is declared
    first as varchar to accomodate all erroneous data loaded
    from input text file.

    sql> select hiredate from emp;

    HIREDATE
    --------
    010103
    -
    013203
    %^$&**

    I want to display only the invalid dates using select statement.
    Can you help me pls.


    Thanks

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Create a function that checks for valid/invalid dates:
    Code:
    CREATE OR REPLACE FUNCTION is_date (p_string VARCHAR2)
    RETURN INTEGER
    AS
      v_dummy DATE;
    BEGIN
      v_dummy := TO_DATE(p_string, 'MMDDRR');
      RETURN 1;
    EXCEPTION
      WHEN OTHERS THEN RETURN 0;
    END;
    Then select all the records with invalid "dates":

    SELECT * FROM emp WHERE is_date(hiredate) = 0;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Aug 2000
    Location
    Jolo, Sulu
    Posts
    639
    IMPRESSIVE! Thanks a lot and God Bless everyone.

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