Column to Rows - Number Separated By Any Character

Value: '374627467(92876/5674)988276+87234687/8974232*3746-984027374[32742-3746236'

Expected Output:
VALUE
------------
374627467
92876
5674
988276
87234687
8974232
3746
984027374
32742
3746236


with t as (
select '374627467(92876/5674)988276+87234687/8974232*3746-984027374[32742-3746236' x from dual
)
select
    substr(   
        ','||x,regexp_instr(','||x,'[^[:digit:]]',1,level)+1,
        regexp_instr(x||',','[^[:digit:]]',1,level) - regexp_instr(','||x,'[^[:digit:]]',1,level)
    ) value
from
    t connect by level <= length(x) - length(regexp_replace(x,'\D')) + 1;
   

Related Links

1. Oracle: Comma Separated String To Rows 10g Onwards
http://nimishgarg.blogspot.com/2010/06/oracle-comma-seprated-string-to-rows_16.html

2. Oracle: Comma Separated String To Rows 11g
http://nimishgarg.blogspot.com/2010/06/oracle-comma-seprated-string-to-rows.html   

3. Oracle: Nth Record from Comma Separated String
http://nimishgarg.blogspot.com/2010/06/oracle-nth-record-from-comma-seprated.html

Oracle: Comma Separated String To Rows
http://nimishgarg.blogspot.com/2009/12/oracle-sql-use-comma-seprated-string-to.html

No comments:

Post a Comment