Write a query to display numeric & character values in separate columns

Source: 

 

Target:

 

Drop table t_da;

create table t_da (value varchar2(20));

insert into t_da values ('100');

insert into t_da values ('aaa');

insert into t_da values ('bbb');

insert into t_da values ('200');

insert into t_da values ('ccc');

commit;

Step1:

select  null as numeric_value, value as character_value from t_da WHERE REGEXP_LIKE(value, '[^[:digit:]]')

union all

select value as numeric_value, null as character_value from t_da WHERE REGEXP_LIKE(value, '[[:digit:]]');

Output:

null  aaa

null  bbb

null  ccc

100   null

200   null

Step2:

select nv.numeric_value, cv.character_value

from

( select  numeric_value , rownum rnum from

(select  null as numeric_value, value as character_value from t_da WHERE REGEXP_LIKE(value, '[^[:digit:]]')

union all

select value as numeric_value, null as character_value from t_da WHERE REGEXP_LIKE(value, '[[:digit:]]')

)

where numeric_value is not null ) nv

full join

(select  character_value , rownum rnum from

(select  null as numeric_value, value

as character_value from t_da WHERE REGEXP_LIKE(value, '[^[:digit:]]')

union all

select value as numeric_value, null as character_value from t_da WHERE REGEXP_LIKE(value, '[[:digit:]]')

)

where character_value is not null ) cv

on nv.rnum=cv.rnum;

Output:

100  aaa

200  bbb

null ccc