SQL: Replacing "CONNECT BY" with recursive subquery

In my previous post, I used "CONNECT BY" clause.

SQL: Delimited list of values as in-line view. csv on memory (INOUE Katsumi @ Tokyo)

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.

About Oracle: Recursive subquery factoring

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
Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today