This content has been marked as final. Show 2 replies
It's very confusing to use "CONNECT BY LEVEL <= x" when there's more than one row.
Generate the highest level you'll ever need in a sub-query (called a counter table ) that is based on a one-row table.
Then join to the counter table in your main query.
Edited by: Frank Kulash on Oct 23, 2009 12:01 PM
WITH cntr AS ( SELECT LEVEL AS n FROM dual CONNECT BY LEVEL <= 1 + ( SELECT MAX ( LENGTH ( REGEXP_REPLACE ( col2 , '[^;]' ) ) ) FROM t ) ) SELECT t.col1 , REGEXP_SUBSTR ( t.col2 , '[^;]+' -- Use + here, not * , 1 , cntr.n ) AS col2_substr FROM t JOIN cntr ON cntr.n <= LENGTH ( REGEXP_REPLACE (col2 || ';', '[^;]')) ORDER BY t.col1 , cntr.n ;
Thanks a lot Frank (espacially for the edited version, the first version returned an error as you probably noticed too).
Works very fast now!