How to convert a list to a table?

We recently discussed the LISTAGG function, which allows you to convert data from a column to a  delimited list. What if we want to convert a list into a table? It's not that easy anymore. There is no inverse function of LISTAGG. We have to write it ourselves. To extract elements from the text, we can use one of the following functions:

1. regexp_substr

2. substr

3. dbms_utility.comma_to_table

REGEXP_SUBSTR

We can use regular expressions to cut the string into elements. Note the NLS_LANGUAGE settings, as regexp functions in non-English languages, can lose a lot of performance.

 declare
 vEl varchar2(32000);  
 nCntElements number;  
 begin  
 nCntElements := length(regexp_replace(pList, '[^,]+') ) + 1;  
   for i in 1..nCntElements loop  
     vEl := regexp_substr(pList, '[^,]+', 1, i);  
   end loop;  
 dbms_output.put_line(nCntElements);  
 end;  

SUBSTR

The good old substr or scratching element by element.

   
 declare
 vEl varchar2(32000);
 nCntElements number := 0;  
 nStartPos number;  
 nEndPos number := 0;  
 begin  
 nStartPos := 1;  
    loop  
     nCntElements := nCntElements +1;  
     nStartPos := nEndPos +1;  
     nEndPos := instr(pList,',', nStartPos);  
     vEl := substr(pList,nStartPos, case when nEndPos= 0 then length(plist) else nEndPos - nStartPos end);     
     exit when nEndPos = 0;  
   end loop;  
   dbms_output.put_line(nCntElements);   
 end;  

dbms_utility.comma_to_table

Built-in Oracle function from dbms_utility package. Unlike the previous ones, this function cannot be used in SQL. The second disadvantage is that only a comma is allowed as a list item separator, and list items must be text that complies with the oracle naming rules, i.e. the item cannot start with a digit or a special character.

 
 declare
 tab dbms_utility.uncl_array;  
 nCntElements number := 0;  
 vEl varchar2(32000);  
 BEGIN  
    dbms_utility.comma_to_table(pList, nCntElements, tab);  
    for i in 1..nCntElements  
    loop  
    vEl := tab(i);  
    end loop;  
    dbms_output.put_line(nCntElements);  
 END;  

We can deal with the limitations by replacing the original separator with a comma and surrounding the elements with quotation marks (here, however, the number of elements allowed may decrease significantly due to the fact that we add additional characters to the variable). Nevertheless, the function works quickly and efficiently.

 tab dbms_utility.uncl_array;  
 declare
 nCntElements number := 0;  
 nEl varchar2(32000);  
 vList varchar2(32000);  
 BEGIN  
   vList := '"'|| replace(pList, ',', '","') ||'"';  
   dbms_utility.comma_to_table(vList, nCntElements, tab);  
    for i in 1..nCntElements  
    loop  
    nEl := trim( '"' FROM tab(i));  
    end loop;  
   dbms_output.put_line(nCntElements);  
  END;  

There is also an inverse function, i.e. dbms_utility.table_to_comma.

What to choose?

I run several performance tests comparing the execution times. I tested lists with one-character elements of length 2000, 5000, 10000, 20000, 32000. For the test simulating a numeric list using the comma_to_table function, the maximum test was a list of 16000 characters. Additionally, I tested the regexp function for Polish and English.

Tests were run on Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production.

RESULTS (time in seconds):

I must admit that the results surprised me a lot. I didn't expect comma_to_table and substr to be practically the same fast. But what surprised me the most is that the regexp function in both language settings is so terribly slow! There is a huge gap between regexp and even plain substr!

I leave it up to you to decide which function to use. Nevertheless, I hope this post will help you a little bit.

Source code:

Specification

Body

Test