3

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
  • 1
    If you don't mind me asking, what does the level column mean? Is it an id? E.G: row 1 = level 1, row 2 = level 2, row n = level n? – LogicalBranch Jul 14 at 18:54
  • 2
    Isn't there a convention of using upper case for the SQL keywords (SELECT, FROM, WHERE, etc.)? – Peter Mortensen Jul 14 at 20:37
  • 1
    @LogicalBranch Yes. It's kind of like an id . you're right. – Pantea Tourang Jul 15 at 4:46
4

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
  • 1
    I'm using Oracle 11-g and it gives me this error : Invalid sql statement . Would you mind editing your answer please? – Pantea Tourang Jul 14 at 10:07
  • 2
    @PanteaTourang There is nothing to edit. The above works fine in 11g as well. The error is thrown most likely by your client handling the statements differently. Use SQL*Plus and Oracle SQL Developer. Anything else is suboptimal. The above is from SQL Developer 19.1. – Balazs Papp Jul 14 at 10:40
3
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.