SQL: if cannot convert to_number set as null

You can create a function that tries to convert the string to a number and catches the exception. Something like

CREATE OR REPLACE FUNCTION my_to_number( p_str IN VARCHAR2 )
  RETURN NUMBER
IS
  l_num NUMBER;
BEGIN
  BEGIN
    l_num := to_number( p_str );
  EXCEPTION
    WHEN others THEN
      l_num := null;
  END;

  RETURN l_num;
END;

Then you can

SELECT id, my_to_number( value )
  FROM your_table

I generally use translate for this because it is such an odd corner case:

SELECT 
  CASE
     WHEN NOT TRIM(TRANSLATE(COLUMN_NAME, '1234567890', ' ')) IS NULL THEN NULL
     ELSE COLUMN_NAME
  END AS "NUMERIC_COLUMN"
FROM
  TABLE_NAME;

If necessary, that can be turned into a procedure, but I'm not sure that there would be terribly much benefit performance-wise.