Split a column into two or more in Oracle

Question

Lets say my table looks like this :

-------------------------
| id | prop             |
-------------------------
| 1  | Jhon_Dhoe_21     |
-------------------------
| 2  | Tom_Dohn_23_MALE |
-------------------------
| 3  | Scot             |
-------------------------

The properties will always be devided by a "_". So after the SELECT the table should look like this:

--------------------------------------
| id | prop1 | prop2 | prop3 | prop4 |
--------------------------------------
| 1  |  Jhon | Dhoe  |  21   |  NULL |
--------------------------------------
| 2  |  Tom  | Dohn  |  23   |  MALE |
--------------------------------------
| 3  |  Scot | NULL  | NULL  |  NULL |
--------------------------------------

Now if we know the maximum number of properties (n) that we could have I suppose we can create a n number of regex expresions over the prop column or something. But if we do not know maybe we have to first find the row with most properties ?

EDIT:

I can't accept multiple rows.

Answer 1

it was an interesting question, so I've solved it this way:

with
  tbl as (
    select 1 id, 'Jhon_Dhoe_21' prop from dual union all
    select 2 id, 'Tom_Dohn_23_MALE' prop from dual union all
    select 3 id, 'Scot' prop from dual
  ),
  maxrows as (select level rn from dual connect by level <= 100)
select id, regexp_substr(t.prop, '[^_]+', 1, mr.rn) prop_rn, rn, prop
from tbl t, maxrows mr
where mr.rn <= regexp_count(t.prop, '\_') + 1
order by id, rn

Results:

        ID PROP_RN                  RN PROP
---------- ---------------- ---------- ----------------
         1 Jhon                      1 Jhon_Dhoe_21
         1 Dhoe                      2 Jhon_Dhoe_21
         1 21                        3 Jhon_Dhoe_21
         2 Tom                       1 Tom_Dohn_23_MALE
         2 Dohn                      2 Tom_Dohn_23_MALE
         2 23                        3 Tom_Dohn_23_MALE
         2 MALE                      4 Tom_Dohn_23_MALE
         3 Scot                      1 Scot

8 rows selected

If you know (or sure) of maximum possible columns, you can use:

with
  tbl as (
    select 1 id, 'Jhon_Dhoe_21' prop from dual union all
    select 2 id, 'Tom_Dohn_23_MALE' prop from dual union all
    select 3 id, 'Scot' prop from dual
  ),
  maxrows as (select level rn from dual connect by level <= 100),
  tbl2 as (
    select id, regexp_substr(t.prop, '[^_]+', 1, mr.rn) prop_rn, rn, prop
    from tbl t, maxrows mr
    where mr.rn <= regexp_count(t.prop, '\_') + 1
    order by id, rn)
select *
from tbl2
pivot (
  max(prop_rn)
  for rn in (1,2,3,4,6,7,8,9,10)
)

Result:

        ID PROP             1                2                3                4                6                7                8                9                10
---------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
         1 Jhon_Dhoe_21     Jhon             Dhoe             21                                                                                                    
         3 Scot             Scot                                                                                                                                    
         2 Tom_Dohn_23_MALE Tom              Dohn             23               MALE                                                                                 

SQL> 

Or use xmltype:

with
  tbl as (
    select 1 id, 'Jhon_Dhoe_21' prop from dual union all
    select 2 id, 'Tom_Dohn_23_MALE' prop from dual union all
    select 3 id, 'Scot' prop from dual
  ),
  maxrows as (select level rn from dual connect by level <= 100),
  tbl2 as (
    select id, regexp_substr(t.prop, '[^_]+', 1, mr.rn) prop_rn, rn, prop
    from tbl t, maxrows mr
    where mr.rn <= regexp_count(t.prop, '\_') + 1
    order by id, rn)
select *
from tbl2
pivot xml (
  max(prop_rn) prp
  for rn in (any)
)