Finding Non Numeric Fields in Oracle

[space_monkey]

In vb and i think even in MS SQL server there is a ISNumeric function built in. I know there's no function like that in Oracle (9i), but i was thinking that there might be a logical way to determine if a field is Numeric or Not. Unfortunetly i can't use a stored procedure to do this either. So i'd have to do it in the sql statement itself.

Does anyone have an idea on how to do this?

Select Field1 from table_name
where field1 is NOT numeric

Thanks
Andy

[abhijit]

Well you could use views like all_tab_columns

Check the data_type column in all_tab_columns.

[space_monkey]

sorry i'm not trying to find the data type i'm trying to determine whether or not a value is numeric. For instance.

Field1
-----
aasdf
sdifiue123
32fafk
12314
asdfkj3

i need a way to filter out the row (12314) and return the rest.

[abhijit]

try this pl / sql block.

begin
    ......
    BEGIN
       l_test_number := l_string_that_might_hold_a_number;
       .... code that should run when it is in fact a number......
    EXCEPTION
       WHEN OTHERS 
       then
          ..... code that should run when it is in fact NOT a number.....
    END;
    .......
end;

[space_monkey]

I saw this on another website, but right now i can't use a stored procedure. So how could i use it in a vb program?

[abhijit]

I made an oracle function out of it.

create or replace function isanumber(x varchar2) return boolean
is
begin
    declare l_test_number number;
    BEGIN
       l_test_number := x;
       return true;
    EXCEPTION
       WHEN OTHERS THEN
	return false;
    END;
end;
/

[GaryMazzone]

What about a simple IsNumeric test?

If Not IsNumeric(field1) then
     Do what you need to do with the record
else
     Throw the record out or do something different with it
end if

[space_monkey]

i think that is what i'm gonna do. I'd like to use the function that abhijit suggested, but i doubt i have permission to create it. I was hoping there would be just a simple thing to add to my select statement, but i guess not. And i thought that i had seen a function before that would allow me to do it but i can't find it anywhere.

[abhijit]

space_monkey hang on for a moment before you create the function. i just tested it and its giving me a problem. so let me fix it and then you can have the function.

[abhijit]

hey I fixed this function. the reason it was throwing an error is that boolean is only available in pl/sql and not in sql. here is my fix.

create or replace function isanumber(x varchar2) return varchar2
is
begin
    declare l_test_number number;
    BEGIN
       l_test_number := x;
       return 'true';
    EXCEPTION
       WHEN OTHERS THEN
	return 'false';
    END;
end;

[space_monkey]

Thanks abhijit, I'll try it out.