How to select multiple columns from dual when I use regexp_substr with connect by clause and level

Question

SELECT DISTINCT REGEXP_SUBSTR ('id1:id2','[^:]+',1,LEVEL)  R ,
  REGEXP_SUBSTR ('name1:name2','[^:]+',1,LEVEL) D
FROM   DUAL
CONNECT BY REGEXP_SUBSTR ('id1:id2','[^:]+',1,LEVEL)
  = REGEXP_SUBSTR ('name1:name2','[^:]+',1,LEVEL) 
order by 1;

My delimiter is ':' I can only get 1 row (id & name) by using the above query but if I want to get multiple rows with multiple columns ( id & name) how should I do it?

Answer 1

Your connect-by condition is only going to be true if the ID and name values match - which is not the case for your examples.

You can count the number of elements instead; and in case the counts are different, use greates() to pick the higher count:

SELECT REGEXP_SUBSTR ('id1:id2','[^:]+',1,LEVEL) R ,
   REGEXP_SUBSTR ('name1:name2','[^:]+',1,LEVEL) D
FROM DUAL
CONNECT BY LEVEL <= GREATEST (REGEXP_COUNT ('id1:id2',':'),
  REGEXP_COUNT ('name1:name2',':')) + 1
order by 1;

R       D          
------- -----------
id1     name1      
id2     name2      

Or a safer pattern if you could have empty elements:

SELECT REGEXP_SUBSTR ('id1:id2:id3', '(.*?)(:|$)', 1, LEVEL, NULL, 1) R ,
  REGEXP_SUBSTR ('name1::name3', '(.*?)(:|$)', 1, LEVEL, NULL, 1) D
FROM DUAL
CONNECT BY LEVEL <= GREATEST (REGEXP_COUNT ('id1:id2:id3',':'),
  REGEXP_COUNT ('name1::name3',':')) + 1
order by 1;

R           D           
----------- ------------
id1         name1       
id2                     
id3         name3