Oracle SQL convert string to number with exceptions to treat text as 0

Question

I would like to convert a string column to number, with ability to handle exceptions, such as text, in Oracle SQL.

The target is to convert text to 0, and number in string format into number.

Example Input:

ID   Column1
1    01A
2    02A
3    1.30
4    1,30
5    100000
6           (Note: 1 empty space)
7           (Note: Null)

Expected output

ID   Column1
1    0
2    0
3    1.3
4    1.3
5    100000
6    0
7    0

I tried the following SQL command:

select ID, to_number(Column1) As Column1 
from Table1

The error code is ORA-01722 if there is any non-numeric output.

The expected result is to get rid of error ORA-01722, even when input has null, space, text (i.e. anything non-numeric)

Answer 1

select ID, to_number(Column1 DEFAULT 0 ON CONVERSION ERROR) As Column1 from Table1

According to this Oracle doc