Finding Non Numeric Fields in Oracle

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

Well you could use views like all_tab_columns

Check the data_type column in all_tab_columns.

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.

try this pl / sql block.

Code:

---

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;

---

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?

I made an oracle function out of it.

Code:

---

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;
/

---

What about a simple IsNumeric test?

VB Code:

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

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.

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.

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.

Code:

---

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;

---

Thanks abhijit, I'll try it out.