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
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
with cte as
( select distinct trim(regexp_substr('1:30,2:4,52:0,8:1,90:54', '[^,]+', 1, level)) repfield,
level lvl
from dual
connect by regexp_substr('1:30,2:4,52:0,8:1,90:54', '[^,]+', 1, level) is not null
order by lvl)
select
SUBSTR(repfield,1,instr(repfield , ':')-1) AS Field1,
SUBSTR(repfield,instr(repfield,':')+1,length(repfield)) as field2,
lvl
from cte
Thanks for you're answer. I came to another way to solve this. I could not use regular expressions (I did not have enough information) so i used SUBSTR and INSTR instead
levelcolumn mean? Is it anid? E.G: row 1 = level 1, row 2 = level 2, row n = level n?