Oracle: Connect by Level & regexp_substr

Question

Hi I have an Oracle query that I did not write and I also do not have access to run in an Oracle environment.

Could someone please explain what this query is doing please?

WITH tableName AS 
( SELECT regexp_substr(fieldName,'[^,]+',1,level) as tableName 
  FROM   (SELECT :tableName as fieldName From DUAL) 
  CONNECT BY LEVEL <= REGEXP_COUNT(fieldName ,'[,]')+1 )

I understand that it is creating a CTE called tableName and that the Oracle allows DUAL to be specified as a table in queries that do not need data from any tables. SO that that the above query is not pulling from any specific table.

However the remainder of the query I do not understand as I am very new to Oracle.

Any help would be appreciated I tried googling but came up blank.

score 4 · Answer 1 · answered Dec 21 '18 at 18:34

Warning: string parsing using the common regex '[^,]+' does not return the expected value if there is a NULL element in the list. The elements will be in the wrong position after the NULL element. Note the second element is NULL but in the result set the elements are returned in the wrong places:

SQL> select level, regexp_substr('emp,,bonus,salgrade','[^,]+',1,level) as tableName
       from dual
       connect by LEVEL <= REGEXP_COUNT('emp,,bonus,salgrade' ,',') + 1;

     LEVEL TABLENAME
---------- -------------------
         1 emp
         2 bonus
         3 salgrade
         4

Use this form of REGXP_SUBSTR() instead which handles the NULL:

SQL> select level, regexp_substr('emp,,bonus,salgrade','(.*?)(,|$)',1,level, NULL, 1) as tableName
       from dual
       connect by LEVEL <= REGEXP_COUNT('emp,,bonus,salgrade' ,',') + 1;

     LEVEL TABLENAME
---------- -------------------
         1 emp
         2
         3 bonus
         4 salgrade

See this post for more info

Littlefoot · Accepted Answer · 2018-12-21T16:30:37.670

It splits comma-separated values string into rows. For example (slightly modified so that it works in SQL*Plus):

SQL> with tableName AS
  2   ( SELECT regexp_substr(fieldName,'[^,]+',1,level) as tableName
  3     FROM (SELECT '&tableName' as fieldName From DUAL)
  4     CONNECT BY LEVEL <= REGEXP_COUNT(fieldName ,'[,]')+1 )
  5  select * From tablename;
Enter value for tablename: little,foot,overflow

TABLENAME
----------------------------------------------------------------------
little
foot
overflow

SQL>

A simpler/shorter version:

SQL> select level, regexp_substr('&&fieldName','[^,]+',1,level) as tableName
  2  from dual
  3  connect by LEVEL <= REGEXP_COUNT('&&fieldName' ,'[,]') + 1;
Enter value for fieldname: emp,dept,bonus,salgrade

     LEVEL TABLENAME
---------- --------------------
         1 emp
         2 dept
         3 bonus
         4 salgrade

SQL>

So, what does it do? REGEXP_COUNT counts number of separators (comma in this case) and it is used in CONNECT BY which is related to hierarchical query whose LEVEL pseudocolumn (I included it into the output of the 2nd example) is used in REGEXP_SUBSTR as its 4th parameter, occurrence.

Oracle: Connect by Level & regexp_substr

2 Answers2