How to get non numeric column values from a table?

[jmodic]

The suggested method will probably recognise the majority of nonnumerical values in your VARCHAR2 column, but it will certanly fail on some strings. For example, it will not find the following few samples to be non-numerical:

909..12
934%12
9716+13
!"#$%&/()=
etc etc... You get the point.

In fact, I think it would be "very hard" to solve this in generic maner only with SQL and built-in functions. It would be much easier to write your own simple PL/SQL function and use this in your SQL:

create or replace function my_to_number
  (p_string in varchar2)
  return number
is
  l_number number := null;
begin
  begin
    l_number := to_number(p_string);
  exception
    when others then null;
  end;
  return l_number;
end;

Now you can write your query to extract only those non-numeric values like:

select * from my_table
where my_column is not null
  and my_to_number(my_column) is null;