Oracle Regular Expression – How to Separate Specific Strings

oracleoracle-11gplsqlregular expressionsplit

I have a string '(1:30,2:4,52:0,8:1)', and I need to use a regular expression to have this output:

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

The query I've wrote so far is:

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

Best Answer

Simple method:

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

Or with fewer regexp_substr calls:

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