sql - Invalid numbers -

sql - Invalid numbers -

so, have column of data, utilize previous example, body temperatures, stored varchar no record gets rejected, however, contains numeric data.

the people sending me info using less perfect system, have wrong data. need write sql query find valid values above or below value.

for example, temps on 104, should indicate either extreme cases or errors.

i tried:

select count(1), result_num vitals test_cd 'temp' , cast(result_num integer) > 104 grouping result_num;

this returned invalid number error, figured had characters on rows couldn't convert integers , found records negative values ("-" before number) , said "null", amended query read:

select count(1), result_num vitals test_cd 'temp' **and result_num not '%-%' , result_num not '%null%'** , cast(result_num integer) > 104 grouping result_num;

...and still returned invalid number error. have triple checked info in result_num field , character responses.

all other responses, whether legit temps or not, numeric no characters other decimals.

do need link "not like" statements in parens or something?

this simple answer, driving me nuts.

you filter out non-numeric values a function reply provides, or regular look - might need tweaking:

select count(1), result_num vitals test_cd = 'temp' , regexp_like(result_num, '^[-]?[0-9]*[\.]?[0-9]*$') , cast(result_num integer) > 104 grouping result_num;

sql fiddle.

that exclude non-numbers (maybe all, i'm not confident - regex isn't strong area), though justin's function safer.

however, there's still no guarantee filter function applied before cast. if still trips utilize subquery filter out non-numeric values , check actual value of remain; you'd need add together hint stop oracle unnesting subquery , changing evaluation order on you.

another approach variation of justin's function returns actual number:

create or replace function safe_number( p_str in varchar2 ) homecoming number deterministic parallel_enable l_num number; begin l_num := to_number( p_str ); homecoming l_num; exception when value_error homecoming null; end safe_number; /

then query can utilize that:

select count(1), result_num vitals test_cd = 'temp' , safe_number(result_num) > 104 grouping result_num;

sql fiddle.

sql oracle ora-01722