Select numbers using conversional function

Problem statement: From a given set in a column of a character data type, select only rows containing numeric values.

SQL> create table t1 ( c1 varchar2(100));

Table created.

SQL> insert into t1 (c1) values ('123');

1 row created.

SQL> insert into t1 (c1) values (null);

1 row created.

SQL> insert into t1 (c1) values ('$$$');

1 row created.

SQL> commit;

Commit complete.

Option 1. Conversional functions

From Oracle version 12.2 there are functions CAST or TO_NUMBER with default value on conversion error

SQL> select cast(c1 as number default 0 on conversion error) from t1
  2  where cast(c1 as number default 0 on conversion error) <> 0;

CAST(C1ASNUMBERDEFAULT0ONCONVERSIONERROR)
-----------------------------------------
                                      123

Option 2. VALIDATE_CONVERSION function

Even more easier, for select / validate only numbers. The function returns 1 if conversion is successful or 0 if it failed.

SQL> select c1 from t1 where  validate_conversion(c1 as number) = 1;

C1
----------
123	

Inspired by article Conversion Function Enhancements in Oracle Database 12c Release 2

https://oracle-base.com/articles/12c/conversion-function-enhancements-12cr2