Pull values that have only numbers PLSQL-CodePudding

Table

Column

output

12345
21232
32432

please help this is in PLSQL

CodePudding user response：

You can use a default null value in a on conversion error clause, then select record where that is not null:

select *
from table
where cast(column AS NUMBER DEFAULT NULL ON CONVERSION ERROR) is not null

CodePudding user response：

With regular expressions:

SQL> with test (col) as
  2    (select '123d4' from dual union all
  3     select '12345' from dual union all
  4     select '21232' from dual union all
  5     select '32432' from dual union all
  6     select 'G4532' from dual union all
  7     select 'Ffh32' from dual union all
  8     select 'H123f' from dual
  9    )
 10  select col
 11  from test
 12  where regexp_like(col, '^\d $');

COL
-----
12345
21232
32432

SQL>