How to check for numbers in a varchar column

If you have a table with a VARCHAR2 column in it, that means it may contain any number of values, i.e. numbers or characters, if you want to check that the column only has numbers or you only want to return numbers, use this function below:

create or replace function is_number
(p_string in varchar2)
return number
deterministic
as
l_num number;
begin
l_num := p_string;
return 1;
exception
when others then
return null;

end;

Now add the following index on the function.

create index t_idx on t(is_number(data));

Now if you query:

SELECT * FROM T WHERE IS_NUMBER(data) = 1

You should get all of the rows with valid numbers in them via the index.