[PLSQL] Split string by delimiter and store it in VARRAY

Here is some sample Oracle PL/SQL code to split a string using a specific delimiter and storing the result pieces in a VARRAY.

 DECLARE  
   
 TYPE t_varchar2_varray IS TABLE OF VARCHAR2(4000);  
 list_val t_varchar2_varray := t_varchar2_varray();  
 l_explode_at PLS_INTEGER;  
   
 l_string VARCHAR2(4000) := your_string;  
 l_separator VARCHAR2(1) := your_separator;  
   
 BEGIN  
   
 l_explode_at := INSTR (l_string, l_separator); -- if the separator is not in the string, it returns 0   
   
 IF l_explode_at != 0 THEN   
        -- split all the values in the list and store them in our temp variable   
      LOOP   
      EXIT WHEN l_string IS NULL; -- keep going as long as there are values in the list   
        
       l_explode_at := INSTR (l_string, l_separator);   
       list_val.EXTEND;   
         
       -- if we have other values after this one   
       IF l_explode_at !=0 THEN   
         
            -- store it and keep going   
            list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, l_explode_at - 1)); --get the current value   
            l_string := SUBSTR (l_string, l_explode_at + 1); -- and move on the the next one   
              
       -- if there are no more values after me   
       ELSE   
            -- store the last one and quit   
            list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, LENGTH(l_string)));   
            l_string := NULL;   
       END IF;   
         
      END LOOP;   
        
 END IF;  
   
 END;  

Additionally, if you want to remove "empty" values (eg: tabs or spaces) from the string, you can add this code initially:

l_string := regexp_replace(l_string, '[[:space:]]*','');