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
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