CODEWITHSUNDEEP
sqloracle-database
Prabhat Bhatt
Prabhat Bhatt

Reputation: 63

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

Upvotes: 1

Views: 275

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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;

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520938

Using ROW_NUMBER with a self join:

WITH cte AS (
    SELECT t.*, REGEXP_LIKE(Col1, '^[[:digit:]]+$') AS is_number,
                ROW_NUMBER() OVER (PARTITION BY REGEXP_LIKE(Col1, '^[[:digit:]]+$') ORDER BY Col1) rn
    FROM yourTable t
)

SELECT t1.Col1, t2.Col1
FROM cte t1
INNER JOIN cte t2
    ON t2.rn = t1.rn
WHERE t1.is_number AND NOT t2.is_number
ORDER BY t1.rn;

Upvotes: 0

Related Questions