-
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
-
how abt using this?
WHERE INSTR(TRANSLATE(COL1, '0123456789', '~~~~~~~~~~'), '~', -1), LENGTH(TRIM(COL1))
hth
Cheers!
OraKid.
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|