Finding non-numeric data within VARCHAR2
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Finding non-numeric data within VARCHAR2

  1. #1
    Join Date
    Nov 2001
    Posts
    13
    Hi,

    I need to find all occurences of VARCHAR2(6) column that contains non-numeric data. The intention being to change the column to NUMBER. Im sure there's a trick to this, but what is it?

    I cant seem to use TO_NUMBER(COL1) without getting ORA-01722: invalid number.

    I tried WHERE TRANSLATE(COL1, '0123456789', '~~~~~~~~~~') <> '~~~~~~'
    but some entries may be less that 6 digits so no good.

    Any advice greatly appreciated!

    Ben


  2. #2
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    how abt using this?

    WHERE INSTR(TRANSLATE(COL1, '0123456789', '~~~~~~~~~~'), '~', -1), LENGTH(TRIM(COL1))

    hth
    Cheers!
    OraKid.

  3. #3
    Join Date
    Nov 2001
    Posts
    13
    Thanks,

    I see where your coming from but the data could well be mixed alpha+numeric within the same row.

    Also, i think there's a mistake in your extract.

    WHERE INSTR(TRANSLATE(COL1, '0123456789', '~~~~~~~~~~'), '~', -1), LENGTH(TRIM(COL1))

    I assume(?) the Length(Trim(COL1)) should have been a comparison with INSTR(...)

    For testing i'm using the values shown below

    000001
    00000a
    1001
    qwe
    a10001

    Using INEQUALITY in your where condition (which I guess is what you meant) only returns values NOT ENDING with a numeric (and misses 'a10001').

    Using EQUALITY in the where condition only me values ENDING with a numeric (and includes 10001).

    I need to find rows where ANY character is non-numeric.

    Cheers

    Ben

  4. #4
    Join Date
    Nov 2001
    Posts
    13
    With a little help from my freinds, i give you.....

    WHERE LENGTH( REPLACE( TRANSLATE( COL1, '0123456789', '0000000000'), 0, NULL) ) >0

    Anything numeric gets translated into zeros.

    Anything zero gets replaced with null.

    If there's anything left it must be non-numeric and it must have length.

    Thanks to Rog for this one!!!

    Cheers

    Ben

  5. #5
    Join Date
    Aug 2000
    Location
    Ny
    Posts
    105
    FUNCTION Is_Number(NUMSTR IN VARCHAR2)
    RETURN NUMBER IS

    RESULT NUMBER;

    BEGIN
    RESULT:=NUMSTR/1;

    RETURN(RESULT);
    EXCEPTION WHEN OTHERS THEN
    RETURN(NULL);
    END Is_Number;






    SELECT COL1
    FROM TABLE_1
    WHERE IS_NUMBER(COL1) IS NULL;

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