SQL- How to get non numeric column values from a table?

Need to identify all the non numeric values in a table column. The requirement is as follows. 

1. Curently the amount is stored in a VARCHAR2 column ( AMOUNT) of table A.

2. All the data in this table needs to be migrated to new tables. In new tables these columns are having data type of NUMBER.Since the source column is having data type of VARCHAR2 some of values in AMOUNT have non numeric characters. This is causing lot of trouble in migrating the data to a NUMBER field. 

3.Giving Invalid_number exception.

Sol:
-----
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:

Code:

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;