Separate numeric values and character values in oracle

A table contains both numeric and character values in a column. The numeric values and character values need to be separated in different columns

Col1
1
2
3
A
B
C

The output needs to be

col1	col2
1	A
2	B
3	C

Answer

You could do this using conditional aggregation:

select max(case when regexp_like(col1, '^[0-9]*$') then col1 end),
       max(case when not regexp_like(col1, '^[0-9]*$') then col1 end)
from (select t.*,
             row_number() over (partition by (case when regexp_like(col1, '^[0-9]*$') then 1 else 0 end)
                                order by col1
                               ) as seqnum
      from t
     ) t
group by seqnum;

You cold simplify this a bit using a lateral join:

select max(case when is_number = 1 then col1 end),
       max(case when is_number = 0 then col1 end)
from (select t.*, x.is_number
             row_number() over (partition by x.is_number
                                order by col1
                               ) as seqnum
      from t cross join lateral
           (select (case when regexp_like(col1, '^[0-9]*$') then 1 else 0 end) as is_number
            from dual
           ) x
     ) t
group by seqnum;