How to Number Seprated By Any Character in Oracle

Introduction

In this tip, I am going to explain to how to separate number separated by any characters in Oracle Database.

Implementation

The implementation is simple and easy, let take look following example string number is contains with different characters

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

Let's see how to separate each number portion from above string using function. Before we write code assume what would be expected results to above string.

Expected Output:

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

PL/SQL Script

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;

Hope helps and thank you for reading.