Invalid number using to_number()

[jdmartin74]

I wonder if anyone has crossed this and has an answer...

I have a query which is doing to_number(field) on one of the columns. My problem is this data (users being users) has some mis-typed values. e.g. '1.r' instead of '1.4'. Obviously, when the query runs, it gets an invalid number.

Is there a function is in Javascript such as isNaN to check if a value is a number of not? I realise I could write a function, but would like to do this in the SQL itself.

Thanks.

J.

 

[nagornyi]

Here is how you can do it:

http://www.arikaplan.com/oracle/ari021000.html

 

[jdmartin74]

Thanks. I've tried this and it helps me out in most circumstances...however, believe it or not, some of my data is just '.' (without quotes). Therefore, I'm still stuck!!

 

[dbtoo2001]

select case when instr(translate('&input1',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ.',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X') = 1 then
case when instr(translate('&&input1',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X') = 0 then to_number('&&input1')
else 0
end
else case when instr(translate('&&input1',
'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ',
'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),'X') > 0 then 0
else to_number('&&input1')
end
end
FROM dual
/

 

[SantaMufasa]

JD,

If all you want is numbers when numbers exists or NULL (when erroneous data exists), then here is a nice little function:

create or replace function get_number_only (str varchar2) return number is
	number_hold	number;
begin
	number_hold	:= to_number(str);
	return number_hold;
exception
	when others then return null;
end;
/

Here are some sample invocations:

select get_number_only('123.45')Good_number, get_number_only('1.r') Bad_number
from dual;

GOOD_NUMBER BAD_NUMBER
----------- ----------
     123.45

Using this function, you should not receive any run-time errors. If you want to pre-filter bad values, you can use this function for filtering where the original value IS NOT NULL and the return value IS NULL.

Let us know what you think,

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:57 (14Jul04) UTC (aka "GMT" and "Zulu"), 14:57 (14Jul04) Mountain Time)

 

[jdmartin74]

Thanks all. I think I will use the function route. I just can't believe there isn't a built in Oracle one.

 

[sem]

Oracle is multilingual environte supporting multiple formats. Actually there's no general way to define whether a string represents number. E.g. 999,999.90 is a valid number for many countries, though in others comma is used as fractional part delimiter, thus this is not a number. Another, even worst case is when 999,999 may be evaluated to difefrent valid numeric values. That's why I suppose Oracle allows us to write our own more-or-less suitable functions.

Regards, Dima

http://seminihin.narod.ru