Convert comma separated string to array in PL/SQL

Question

How do I convert a comma separated string to a array?

I have the input '1,2,3' , and I need to convert it into an array.

Answer 1

I was looking for a similar solution where I had multi-byte characters (hyphen, whitespace, underscore) in comma separated lists. So dbms_utility.comma_to_table didn't work for me.

declare
  curr_val varchar2 (255 byte);
  input_str varchar2 (255 byte);
  remaining_str varchar2 (255 byte);
begin
  remaining_str := input_str || ',dummy';  -- this value won't output
  while (regexp_like (remaining_str, '.+,.+'))
  loop
    curr_val := substr (remaining_str, 1, instr (remaining_str, ',') - 1);
    remaining_str = substr (remaining_str, instr (remaining_str, ',') + 1);
    dbms_output.put_line (curr_val);
  end loop;
end;

This is not an expert answer so I hope someone would improve this answer.

Answer 2

I know Stack Overflow frowns on pasting URLs without explanations, but this particular page has a few really good options:

http://www.oratechinfo.co.uk/delimited_lists_to_collections.html

I particularly like this one, which converts the delimited list into a temporary table you can run queries against:

/* Create the output TYPE, here using a VARCHAR2(100) nested table type */

SQL> CREATE TYPE test_type AS TABLE OF VARCHAR2(100);
  2  /

Type created.

/* Now, create the function.*/

SQL> CREATE OR REPLACE FUNCTION f_convert(p_list IN VARCHAR2)
  2    RETURN test_type
  3  AS
  4    l_string       VARCHAR2(32767) := p_list || ',';
  5    l_comma_index  PLS_INTEGER;
  6    l_index        PLS_INTEGER := 1;
  7    l_tab          test_type := test_type();
  8  BEGIN
  9    LOOP
 10      l_comma_index := INSTR(l_string, ',', l_index);
 11      EXIT WHEN l_comma_index = 0;
 12      l_tab.EXTEND;
 13      l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
 14      l_index := l_comma_index + 1;
 15    END LOOP;
 16    RETURN l_tab;
 17  END f_convert;
 18  /

Function created.

---

/* Prove it works */

SQL> SELECT * FROM TABLE(f_convert('AAA,BBB,CCC,D'));

COLUMN_VALUE
--------------------------------------------------------------------------------
AAA
BBB
CCC
D

4 rows selected.

Answer 3

declare 
    v_str varchar2(100) := '1,2,3,4,6,7,8,9,0,';
    v_str1 varchar2(100); 
    v_comma_pos number := 0;    
    v_start_pos number := 1;
begin             
    loop        
    v_comma_pos := instr(v_str,',',v_start_pos);   
    v_str1 := substr(v_str,v_start_pos,(v_comma_pos - v_start_pos)); 
    dbms_output.put_line(v_str1);     
    if  v_comma_pos = 0 then     
    v_str1 := substr(v_str,v_start_pos);  
    dbms_output.put_line(v_str1);    
    exit;
    end if;    
    v_start_pos := v_comma_pos + 1;  
    if  v_comma_pos = 0 then    
    exit;
    end if;       
    end loop; 
end;

Answer 4

Oracle provides the builtin function DBMS_UTILITY.COMMA_TO_TABLE.

Unfortunately, this one doesn't work with numbers:

SQL> declare
  2    l_input varchar2(4000) := '1,2,3';
  3    l_count binary_integer;
  4    l_array dbms_utility.lname_array;
  5  begin
  6    dbms_utility.comma_to_table
  7    ( list   => l_input
  8    , tablen => l_count
  9    , tab    => l_array
 10    );
 11    dbms_output.put_line(l_count);
 12    for i in 1 .. l_count
 13    loop
 14      dbms_output.put_line
 15      ( 'Element ' || to_char(i) ||
 16        ' of array contains: ' ||
 17        l_array(i)
 18      );
 19    end loop;
 20  end;
 21  /
declare
*
ERROR at line 1:
ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 132
ORA-06512: at "SYS.DBMS_UTILITY", line 164
ORA-06512: at "SYS.DBMS_UTILITY", line 218
ORA-06512: at line 6

But with a little trick to prefix the elements with an 'x', it works:

SQL> declare
  2    l_input varchar2(4000) := '1,2,3';
  3    l_count binary_integer;
  4    l_array dbms_utility.lname_array;
  5  begin
  6    dbms_utility.comma_to_table
  7    ( list   => regexp_replace(l_input,'(^|,)','\1x')
  8    , tablen => l_count
  9    , tab    => l_array
 10    );
 11    dbms_output.put_line(l_count);
 12    for i in 1 .. l_count
 13    loop
 14      dbms_output.put_line
 15      ( 'Element ' || to_char(i) ||
 16        ' of array contains: ' ||
 17        substr(l_array(i),2)
 18      );
 19    end loop;
 20  end;
 21  /
3
Element 1 of array contains: 1
Element 2 of array contains: 2
Element 3 of array contains: 3

PL/SQL procedure successfully completed.

Regards, Rob.

Answer 5

TYPE string_aa IS TABLE OF VARCHAR2(32767) INDEX BY PLS_INTEGER;

FUNCTION string_to_list(p_string_in IN VARCHAR2)
    RETURN string_aa
    IS
      TYPE ref_cursor IS ref cursor;
      l_cur    ref_cursor;
      l_strlist string_aa;
      l_x      PLS_INTEGER;
    BEGIN      
      IF p_string_in IS NOT NULL THEN 
         OPEN l_cur FOR 
            SELECT regexp_substr(p_string_in,'[^,]+', 1, level) FROM dual
            CONNECT BY regexp_substr(p_string_in, '[^,]+', 1, level) IS NOT NULL;      
         l_x := 1; 
         LOOP
           FETCH l_cur INTO l_strlist(l_x);
           EXIT WHEN l_cur%notfound;
           -- excludes NULL items  e.g.   1,2,,,,5,6,7
           l_x := l_x + 1;
         END LOOP;
      END IF;   
      RETURN l_strlist;
   END string_to_list;

Answer 6

Simple Code

    create or replace function get_token(text_is varchar2, token_in number, delim_is varchar2 := ';') return varchar2 is
       text_ls varchar2(2000);
       spos_ln number;
       epos    _ln number;
    begin
       text_ls := delim_is || text_is || rpad(delim_is, token_in, delim_is);
       spos_ln := instr(text_ls, delim_is, 1, token_in);
       epos_ln := instr(text_ls, delim_is, 1, token_in+1);
       return substr(text_ls, spos_ln+1, epos_ln-spos_ln-1);
    end get_token;