如何使用“正則表達式”分隔Oracle中的特定字元串

我有一個 string '(1:30,2:4,52:0,8:1)'，我需要使用正則表達式來獲得這個輸出：

field1   field2  level
 1        30      1
 2         4      2
 52        0      3
 8         1      4

到目前為止我寫的查詢是：

select distinct trim(regexp_substr('1:30,2:4,52:0,8:1','[^:,]+',1,level)) repfield,level lvl
from dual
connect by regexp_substr('1:30,2:4,52:0,8:1', '[^:,]+', 1, level) is not null
order by lvl

簡單方法：

col field1 format a6
col field2 format a6
col lvl format a3
variable B1 varchar2(32);
exec :B1 := '1:30,2:4,52:0,8:1';
select
 regexp_substr(regexp_substr(:B1, '[^,]+', 1, level), '[^:]+', 1) field1,
 regexp_substr(regexp_substr(:B1, '[^,]+', 1, level), '[^:]+', 2) field2, 
 level lvl from dual
connect by regexp_substr(:B1, '[^,]+', 1, level) is not null;

FIELD1 FIELD2 LVL
------ ------ ---
1      30       1
2      4        2
52     2        3
8      1        4

或者更少的regexp_substr呼叫：

col field1 format a6
col field2 format a6
col lvl format a3
variable B1 varchar2(32);
exec :B1 := '1:30,2:4,52:0,8:1';
select
 regexp_substr(:B1, '[^:,]+', 1, level*2 - 1 ) field1,
 regexp_substr(:B1, '[^:,]+', 1, level*2) field2,
 level lvl from dual
connect by regexp_substr(:B1, '[^,]+', 1, level) is not null;

FIELD1 FIELD2 LVL
------ ------ ---
1      30       1
2      4        2
52     0        3
8      1        4

引用自：https://dba.stackexchange.com/questions/242819