SQL: Replacing "CONNECT BY" with recursive subquery
By Katsumii-Oracle on Apr 15, 2013
In my previous post, I used "CONNECT BY" clause.
select regexp_substr('KING,TURNER,BLAH,!ERROR!,0,JAMES','[^,]+',1,level) as token
from dual connect by level <= 256
But after reading very informative blog page below, I decided to rewrite it.
Version 11g release 2 introduced recursive subquery factoring or the recursive with clause.
Here's my result. I stared at this SQL for 10 minutes but I didn't come up with any smarter one.
Of course, I may be wrong.
SQL> var string varchar2(256) SQL> exec :string := 'aa,b_b,cc cc,dddd:ddd' PL/SQL procedure successfully completed. SQL> with tokens(token, level_num) as 2 (select regexp_substr(:string,'[^,]+',1,1),1 from dual 3 union all 4 select regexp_substr(:string,'[^,]+',1,level_num+1),level_num+1 from tokens 5 where level_num < 256) 6 select token from tokens where token is not null 7 / aa b_b cc cc dddd:ddd