sql - Split a column into two or more in Oracle -

lets table looks :

------------------------- | id | prop             | ------------------------- | 1  | jhon_dhoe_21     | ------------------------- | 2  | tom_dohn_23_male | ------------------------- | 3  | scot             | ------------------------- 

the properties devided "_". after select table should this:

-------------------------------------- | id | prop1 | prop2 | prop3 | prop4 | -------------------------------------- | 1  |  jhon | dhoe  |  21   |  null | -------------------------------------- | 2  |  tom  | dohn  |  23   |  male | -------------------------------------- | 3  |  scot | null  | null  |  null | -------------------------------------- 

now if know maximum number of properties (n) have suppose can create n number of regex expresions on prop column or something. if not know maybe have first find row properties ?

edit:

i can't accept multiple rows.

it interesting question, i've solved way:

with   tbl (     select 1 id, 'jhon_dhoe_21' prop dual union     select 2 id, 'tom_dohn_23_male' prop dual union     select 3 id, 'scot' prop dual   ),   maxrows (select level rn dual connect level <= 100) select id, regexp_substr(t.prop, '[^_]+', 1, mr.rn) prop_rn, rn, prop tbl t, maxrows mr mr.rn <= regexp_count(t.prop, '\_') + 1 order 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 know (or sure) of maximum possible columns, can use:

with   tbl (     select 1 id, 'jhon_dhoe_21' prop dual union     select 2 id, 'tom_dohn_23_male' prop dual union     select 3 id, 'scot' prop dual   ),   maxrows (select level rn dual connect level <= 100),   tbl2 (     select id, regexp_substr(t.prop, '[^_]+', 1, mr.rn) prop_rn, rn, prop     tbl t, maxrows mr     mr.rn <= regexp_count(t.prop, '\_') + 1     order id, rn) select * tbl2 pivot (   max(prop_rn)   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 (     select 1 id, 'jhon_dhoe_21' prop dual union     select 2 id, 'tom_dohn_23_male' prop dual union     select 3 id, 'scot' prop dual   ),   maxrows (select level rn dual connect level <= 100),   tbl2 (     select id, regexp_substr(t.prop, '[^_]+', 1, mr.rn) prop_rn, rn, prop     tbl t, maxrows mr     mr.rn <= regexp_count(t.prop, '\_') + 1     order id, rn) select * tbl2 pivot xml (   max(prop_rn) prp   rn in (any) )