Splitting a String into Elements

Every 4 months or so I need a simple way to split a string (VARCHAR2) into elements, where the elements are separated with some fixed value (a comma, a colon, or perhaps a longer string). Since my short-term memory is too short0, I figured I should make a reminder here. Of course, you’ll find this on Stackoverflow as well.

There is this function in APEX, which is usually1 available for you in the database, even if you are not using APEX. Here is a short demo:

declare  
  l_elements_arr apex_application_global.vcarr2;  
  l_str varchar2(2000) := 'IPAStoutPorterPale Ale';  
  l_sep varchar2(10) := '';  
begin  
  l_elements_arr := apex_util.string_to_table(l_str, l_sep);  
  for i in 1..l_elements_arr.count loop  
    dbms_output.put_line(l_elements_arr(i));  
  end loop;  
  l_str := apex_util.table_to_string(l_elements_arr,'|');  
  dbms_output.put_line(l_str);  
end;  
/  

The variables for the string and the separator can of course be part of a procedure / function. Also not that the separator can have more than one character. I used three stars because beer is probably good for you.

Another demo inspired by this Oracle blog post :

declare   
  l_str varchar2(100) := 'I$$bought$$a$$3D printer$$this weekend';  
  l_sep varchar2(10) := '$$';  
  l_exp varchar2(200) := '^' || lsep || '+' ;  
begin  
  for e in (select regexp_substr(l_str,l_exp, 1, level) str  
    from dual  
    connect by regexp_substr(l_str,l_exp, 1, level) is not null) loop  
    dbms_output.put_line(e.str);  
  end loop;  
end;  
/  

Since the work is done by the SQL statement in the FOR-LOOP, you can easily use this in pure SQL as well.

0. Let’s just say that my buffer cache has surrendered space to the library cache, because there is so much interesting stuff going on, and all that parsing takes space. 
1. “Usually”, because APEX is installed when creating the database, unless you deselect one of the standard components, which would, in almost all cases (that is, more often than what “usually” means), would be a mistake. If you regret your mistake, just download the latest version and install it. That will take less time than it takes to teach me write proper sentences with an adequate use of strange words, and commas.