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.

Preoccupy

Answer 1

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.

Nonappearance

Good catch. It also doesn't work with special characters. You could work around this limitation by doing some extra special "replaces". For example, use replace(...,' ','XYZ') when entering the function and replace(...,'XYZ',' ') when retrieving the individual values. – Nonappearance

From: Converting delimited lists to collections (and vice versa) COMMA_TO_TABLE (and the reverse TABLE_TO_COMMA) are not written for this purpose! ... They are written primarily for use within replication internally by Oracle, and parse IDENTIFIERS rather than strings, and as such have to be valid Oracle object names. – Distilled

Error when used a string with "." Ex: '8.5.17.1,8.5.17.2' Error is ORA-20001: comma-separated list invalid near x8.5. Can you help resolve this??? – Alight

The functions used here are merely useful to split a list of table-names or similar valid object names/identifiers. See the Oracle documentation. Do not follow this solution, as it will result in undefined behaviour for "random"/"arbitrary" string-lists. – Tilden

Answer 2

here is another easier option

select to_number(column_value) as IDs from xmltable('1,2,3,4,5');

Fluviatile

Wow! That just reads so nicely for PL/SQL: FOR i IN (SELECT to_number(column_value) as ID FROM xmltable('1,2,3,4,5')) LOOP... END LOOP; is just most excellent, thank you! – Merilyn

Alas, this gives ORA-01460: unimplemented or unreasonable conversion requested for a line >4,000 bytes long – Sematic

Answer 3

We can never run out of alternatives of doing the same thing differently, right? I recently found this is pretty handy:

DECLARE
   BAR   VARCHAR2 (200) := '1,2,3';
BEGIN
   FOR FOO IN (    SELECT REGEXP_SUBSTR (BAR,
                                         '[^,]+',
                                         1,
                                         LEVEL)
                             TXT
                     FROM DUAL
               CONNECT BY REGEXP_SUBSTR (BAR,
                                         '[^,]+',
                                         1,
                                         LEVEL)
                             IS NOT NULL)
   LOOP
      DBMS_OUTPUT.PUT_LINE (FOO.TXT);
   END LOOP;
END;

Outputs:

1
2
3

Linkman

Very nice and logically built expression. :-) – Custer

Doesn't work if you have empty string in the middle (i.e '1,2,,3') – Gaff

This works efficiently only for small lists as it produces a cross-join before selecting rows where the corresponding match count (LEVEL) is the same. If your lists are likely to grow over time this presents a scalability risk. – Distilled

@Gaff could be solved with some cleaning before hand, replace ,, with , – Emmanuelemmeline

Answer 4

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.

Ben

This solution is the only one which works if you have a string with spaces and consecutive commas (i.e. 12 3,456,,abc,def). Searched for 4 hours, till I fount this!!!!!! – Gaff

Answer 5

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;

Younker

This function doesn't answer the question. The post asks for an array of all elements in the list. This function provides access to a single element in the array by index. – Distilled

Answer 6

Yes, it is very frustrating that dbms_utility.comma_to_table only supports comma delimieted lists and then only when elements in the list are valid PL/SQL identifies (so numbers cause an error).

I have created a generic parsing package that will do what you need (pasted below). It is part of my "demo.zip" file, a repository of over 2000 files that support my training materials, all available at PL/SQL Obsession: www.toadworld.com/SF.

Regards, Steven Feuerstein www.plsqlchallenge.com (daily PL/SQL quiz)

    CREATE OR REPLACE PACKAGE parse
/*
   Generalized delimited string parsing package

   Author: Steven Feuerstein, [email protected]

   Latest version always available on PL/SQL Obsession: 

   www.ToadWorld.com/SF

   Click on "Trainings, Seminars and Presentations" and
   then download the demo.zip file.

   Modification History
      Date          Change
      10-APR-2009   Add support for nested list variations

   Notes:
     * This package does not validate correct use of delimiters.
       It assumes valid construction of lists.
     * Import the Q##PARSE.qut file into an installation of 
       Quest Code Tester 1.8.3 or higher in order to run
       the regression test for this package.

*/
IS
   SUBTYPE maxvarchar2_t IS VARCHAR2 (32767);

   /*
   Each of the collection types below correspond to (are returned by)
   one of the parse functions.

   items_tt - a simple list of strings
   nested_items_tt - a list of lists of strings
   named_nested_items_tt - a list of named lists of strings

   This last type also demonstrates the power and elegance of string-indexed
   collections. The name of the list of elements is the index value for
   the "outer" collection.
   */
   TYPE items_tt IS TABLE OF maxvarchar2_t
                       INDEX BY PLS_INTEGER;

   TYPE nested_items_tt IS TABLE OF items_tt
                              INDEX BY PLS_INTEGER;

   TYPE named_nested_items_tt IS TABLE OF items_tt
                                    INDEX BY maxvarchar2_t;

   /*
   Parse lists with a single delimiter.
   Example: a,b,c,d

   Here is an example of using this function:

   DECLARE
      l_list parse.items_tt;
   BEGIN
      l_list := parse.string_to_list ('a,b,c,d', ',');
   END;
   */
   FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
      RETURN items_tt;

   /*
   Parse lists with nested delimiters.
   Example: a,b,c,d|1,2,3|x,y,z

   Here is an example of using this function:

   DECLARE
      l_list parse.nested_items_tt;
   BEGIN
      l_list := parse.string_to_list ('a,b,c,d|1,2,3,4', '|', ',');
   END;
   */
   FUNCTION string_to_list (string_in      IN VARCHAR2
                          , outer_delim_in IN VARCHAR2
                          , inner_delim_in IN VARCHAR2
                           )
      RETURN nested_items_tt;

   /*
   Parse named lists with nested delimiters.
   Example: letters:a,b,c,d|numbers:1,2,3|names:steven,george

   Here is an example of using this function:

   DECLARE
      l_list parse.named_nested_items_tt;
   BEGIN
   l_list := parse.string_to_list ('letters:a,b,c,d|numbers:1,2,3,4', '|', ':', ',');
   END;
   */
   FUNCTION string_to_list (string_in      IN VARCHAR2
                          , outer_delim_in IN VARCHAR2
                          , name_delim_in  IN VARCHAR2
                          , inner_delim_in IN VARCHAR2
                           )
      RETURN named_nested_items_tt;

   PROCEDURE display_list (string_in IN VARCHAR2
                         , delim_in  IN VARCHAR2:= ','
                          );

   PROCEDURE display_list (string_in      IN VARCHAR2
                         , outer_delim_in IN VARCHAR2
                         , inner_delim_in IN VARCHAR2
                          );

   PROCEDURE display_list (string_in      IN VARCHAR2
                         , outer_delim_in IN VARCHAR2
                         , name_delim_in  IN VARCHAR2
                         , inner_delim_in IN VARCHAR2
                          );

   PROCEDURE show_variations;

   /* Helper function for automated testing */
   FUNCTION nested_eq (list1_in    IN items_tt
                     , list2_in    IN items_tt
                     , nulls_eq_in IN BOOLEAN
                      )
      RETURN BOOLEAN;

END parse;
/

CREATE OR REPLACE PACKAGE BODY parse
IS
   FUNCTION string_to_list (string_in IN VARCHAR2, delim_in IN VARCHAR2)
      RETURN items_tt
   IS
      c_end_of_list   CONSTANT PLS_INTEGER := -99;
      l_item          maxvarchar2_t;
      l_startloc      PLS_INTEGER := 1;
      items_out       items_tt;

      PROCEDURE add_item (item_in IN VARCHAR2)
      IS
      BEGIN
         IF item_in = delim_in
         THEN
            /* We don't put delimiters into the collection. */
            NULL;
         ELSE
            items_out (items_out.COUNT + 1) := item_in;
         END IF;
      END;

      PROCEDURE get_next_item (string_in         IN     VARCHAR2
                             , start_location_io IN OUT PLS_INTEGER
                             , item_out             OUT VARCHAR2
                              )
      IS
         l_loc   PLS_INTEGER;
      BEGIN
         l_loc := INSTR (string_in, delim_in, start_location_io);

         IF l_loc = start_location_io
         THEN
            /* A null item (two consecutive delimiters) */
            item_out := NULL;
         ELSIF l_loc = 0
         THEN
            /* We are at the last item in the list. */
            item_out := SUBSTR (string_in, start_location_io);
         ELSE
            /* Extract the element between the two positions. */
            item_out :=
               SUBSTR (string_in
                     , start_location_io
                     , l_loc - start_location_io
                      );
         END IF;

         IF l_loc = 0
         THEN
            /* If the delimiter was not found, send back indication
               that we are at the end of the list. */

            start_location_io := c_end_of_list;
         ELSE
            /* Move the starting point for the INSTR search forward. */
            start_location_io := l_loc + 1;
         END IF;
      END get_next_item;
   BEGIN
      IF string_in IS NULL OR delim_in IS NULL
      THEN
         /* Nothing to do except pass back the empty collection. */
         NULL;
      ELSE
         LOOP
            get_next_item (string_in, l_startloc, l_item);
            add_item (l_item);
            EXIT WHEN l_startloc = c_end_of_list;
         END LOOP;
      END IF;

      RETURN items_out;
   END string_to_list;

   FUNCTION string_to_list (string_in      IN VARCHAR2
                          , outer_delim_in IN VARCHAR2
                          , inner_delim_in IN VARCHAR2
                           )
      RETURN nested_items_tt
   IS
      l_elements   items_tt;
      l_return     nested_items_tt;
   BEGIN
      /* Separate out the different lists. */
      l_elements := string_to_list (string_in, outer_delim_in);

      /* For each list, parse out the separate items
         and add them to the end of the list of items
         for that list. */   
      FOR indx IN 1 .. l_elements.COUNT
      LOOP
         l_return (l_return.COUNT + 1) :=
            string_to_list (l_elements (indx), inner_delim_in);
      END LOOP;

      RETURN l_return;
   END string_to_list;

   FUNCTION string_to_list (string_in      IN VARCHAR2
                          , outer_delim_in IN VARCHAR2
                          , name_delim_in  IN VARCHAR2
                          , inner_delim_in IN VARCHAR2
                           )
      RETURN named_nested_items_tt
   IS
      c_name_position constant pls_integer := 1;
      c_items_position constant pls_integer := 2;
      l_elements          items_tt;
      l_name_and_values   items_tt;
      l_return            named_nested_items_tt;
   BEGIN
      /* Separate out the different lists. */
      l_elements := string_to_list (string_in, outer_delim_in);

      FOR indx IN 1 .. l_elements.COUNT
      LOOP
         /* Extract the name and the list of items that go with 
            the name. This collection always has just two elements:
              index 1 - the name
              index 2 - the list of values
         */
         l_name_and_values :=
            string_to_list (l_elements (indx), name_delim_in);
         /*
         Use the name as the index value for this list.
         */
         l_return (l_name_and_values (c_name_position)) :=
            string_to_list (l_name_and_values (c_items_position), inner_delim_in);
      END LOOP;

      RETURN l_return;
   END string_to_list;

   PROCEDURE display_list (string_in IN VARCHAR2
                         , delim_in  IN VARCHAR2:= ','
                          )
   IS
      l_items   items_tt;
   BEGIN
      DBMS_OUTPUT.put_line (
         'Parse "' || string_in || '" using "' || delim_in || '"'
      );

      l_items := string_to_list (string_in, delim_in);

      FOR indx IN 1 .. l_items.COUNT
      LOOP
         DBMS_OUTPUT.put_line ('> ' || indx || ' = ' || l_items (indx));
      END LOOP;
   END display_list;

   PROCEDURE display_list (string_in      IN VARCHAR2
                         , outer_delim_in IN VARCHAR2
                         , inner_delim_in IN VARCHAR2
                          )
   IS
      l_items   nested_items_tt;
   BEGIN
      DBMS_OUTPUT.put_line(   'Parse "'
                           || string_in
                           || '" using "'
                           || outer_delim_in
                           || '-'
                           || inner_delim_in
                           || '"');
      l_items := string_to_list (string_in, outer_delim_in, inner_delim_in);


      FOR outer_index IN 1 .. l_items.COUNT
      LOOP
         DBMS_OUTPUT.put_line(   'List '
                              || outer_index
                              || ' contains '
                              || l_items (outer_index).COUNT
                              || ' elements');

         FOR inner_index IN 1 .. l_items (outer_index).COUNT
         LOOP
            DBMS_OUTPUT.put_line(   '> Value '
                                 || inner_index
                                 || ' = '
                                 || l_items (outer_index) (inner_index));
         END LOOP;
      END LOOP;
   END display_list;

   PROCEDURE display_list (string_in      IN VARCHAR2
                         , outer_delim_in IN VARCHAR2
                         , name_delim_in  IN VARCHAR2
                         , inner_delim_in IN VARCHAR2
                          )
   IS
      l_items   named_nested_items_tt;
      l_index   maxvarchar2_t;
   BEGIN
      DBMS_OUTPUT.put_line(   'Parse "'
                           || string_in
                           || '" using "'
                           || outer_delim_in
                           || '-'
                           || name_delim_in
                           || '-'
                           || inner_delim_in
                           || '"');
      l_items :=
         string_to_list (string_in
                       , outer_delim_in
                       , name_delim_in
                       , inner_delim_in
                        );

      l_index := l_items.FIRST;

      WHILE (l_index IS NOT NULL)
      LOOP
         DBMS_OUTPUT.put_line(   'List "'
                              || l_index
                              || '" contains '
                              || l_items (l_index).COUNT
                              || ' elements');

         FOR inner_index IN 1 .. l_items (l_index).COUNT
         LOOP
            DBMS_OUTPUT.put_line(   '> Value '
                                 || inner_index
                                 || ' = '
                                 || l_items (l_index) (inner_index));
         END LOOP;

         l_index := l_items.NEXT (l_index);
      END LOOP;
   END display_list;

   PROCEDURE show_variations
   IS
      PROCEDURE show_header (title_in IN VARCHAR2)
      IS
      BEGIN
         DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
         DBMS_OUTPUT.put_line (title_in);
         DBMS_OUTPUT.put_line (RPAD ('=', 60, '='));
      END show_header;
   BEGIN
      show_header ('Single Delimiter Lists');
      display_list ('a,b,c');
      display_list ('a;b;c', ';');
      display_list ('a,,b,c');
      display_list (',,b,c,,');

      show_header ('Nested Lists');
      display_list ('a,b,c,d|1,2,3|x,y,z', '|', ',');

      show_header ('Named, Nested Lists');
      display_list ('letters:a,b,c,d|numbers:1,2,3|names:steven,george'
                  , '|'
                  , ':'
                  , ','
                   );
   END;

   FUNCTION nested_eq (list1_in    IN items_tt
                     , list2_in    IN items_tt
                     , nulls_eq_in IN BOOLEAN
                      )
      RETURN BOOLEAN
   IS
      l_return   BOOLEAN := list1_in.COUNT = list2_in.COUNT;
      l_index    PLS_INTEGER := 1;
   BEGIN
      WHILE (l_return AND l_index IS NOT NULL)
      LOOP
         l_return := list1_in (l_index) = list2_in (l_index);
         l_index := list1_in.NEXT (l_index);
      END LOOP;

      RETURN l_return;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN FALSE;
   END nested_eq;
END;
/

Televisor

toadworld.com/platforms/oracle/b/weblog/archive/2014/06/27/… new location of demo.zip can be found in this post – Astrogeology

Answer 7

Using a pipelined table function:

SQL> CREATE OR REPLACE TYPE test_type
  2  AS
  3    TABLE OF VARCHAR2(100)
  4  /

Type created.

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

Function created.

Let's see the output:

SQL> SELECT *
  2  FROM TABLE(comma_to_table('12 3,456,,,,,abc,def'))
  3  /

COLUMN_VALUE
------------------------------------------------------------------------------
12 3
456




abc
def

8 rows selected.

SQL>

Mossbunker

Answer 8

I just want to write more clearly Richard and Mike's answers.

Method 1: VARRAY USAGE

DECLARE
 TYPE v_array_type IS VARRAY (10) OF NUMBER;
 var v_array_type;
begin
 select to_number(column_value)  BULK COLLECT into var from  
 xmltable('1,2,3,4,5,6,7,8,9,10');
 for i in 1..var.count LOOP
  dbms_output.put_line(var(i));
 END LOOP;
end;

But in this method you must declare array size. If you want more dynamic method you could use Mike's method.

METHOD 2:

begin
 FOR obj IN (SELECT TO_NUMBER(column_value) as ID FROM 
 xmltable('1,2,3,4,5,6,7,8,9,10,11')) LOOP
  dbms_output.put_line(obj.ID);
 END LOOP;
end;

Confiding

Answer 9

A quick search on my BBDD took me to a function called split:

create or replace function split
( 
p_list varchar2, 
p_del varchar2 := ','
) 
return split_tbl pipelined
is 
l_idx pls_integer; 
l_list varchar2(32767) := p_list;AA 
l_value varchar2(32767);
begin 
loop 
l_idx := instr(l_list,p_del); 
if l_idx > 0 then 
pipe row(substr(l_list,1,l_idx-1)); 
l_list := substr(l_list,l_idx+length(p_del));
else 
pipe row(l_list); 
exit; 
end if; 
end loop; 
return;
end split;

I don't know if it'll be of use, but we found it here...

Felipa

Seems the underlying functions in both our answers are exactly the same :) – Deci

Uops! I did not see your answer! It works quite well actually, I have it stored in my Usefull Functions library ;) – Felipa

Answer 10

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.

Eire

Answer 11

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;

Prolific

Answer 12

you can use oracle method

 declare
  myarray apex_application_global.vc_arr2;
 begin
  myarray = apex_util.string_to_table('1,2,3', ',');//you can use any symbols instead of ',' for split

  for i in 1..myarray.COUNT
    loop
      dbms_output.put_line(myarray(i));
    end  loop;

Dingle

Answer 13

Another possibility is:

create or replace FUNCTION getNth (
  input varchar2,
  nth number
) RETURN varchar2 AS
  nthVal varchar2(80);
BEGIN
  with candidates (s,e,n) as (
      select 1, instr(input,',',1), 1 from dual
      union all
      select e+1, instr(input,',',e+1), n+1
        from candidates where e > 0)
  select substr(input,s,case when e > 0 then e-s else length(input) end) 
    into nthVal
    from candidates where n=nth;
  return nthVal;
END getNth;

It's a little too expensive to run, as it computes the complete split every time the caller asks for one of the items in there...

Casemaker

Answer 14

The solution presented below by Stewart Ashton in this link is pretty handy. He eliminates the need for the value list to be integer, so you can use string list.

In the WITH clause, he surrounds the values with single quotes then converts it to a table having a single column of type VARCHAR2. https://stewashton.wordpress.com

with data as (
  select '"'||replace(:txt, ',', '","')||'"' str from dual
)
select xmlcast(column_value as varchar2(4000)) subs
from data, xmltable(str);

Somnambulism

Answer 15

declare
seprator varchar2(1):=',';
dosweeklist varchar2(4000):='a,b,c';
begin
for i in (SELECT  SUBSTR(dosweeklist,
                         case when level=1 then 1 else INSTR(dosweeklist,seprator,1,LEVEL-1)+1 end,
                         NVL(NULLIF(INSTR(dosweeklist,seprator,1,LEVEL),0),length(dosweeklist)+1) - case when level=1 then 1 else INSTR(dosweeklist,seprator,1,LEVEL-1)+1 end) dat 
          FROM dual
          CONNECT BY LEVEL <= LENGTH(dosweeklist) - LENGTH(REPLACE(dosweeklist,seprator,'')) +1)
loop
dbms_output.put_line(i.dat);
end loop;
end;
/

so select query only in for loop can do the trick, by replacing dosweeklist as your delimited string and seprator as your delimited character.

Lets see output

a

b

c

Interlining

Answer 16

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;

Vegetation

Answer 17

You can use Replace Function to replace comma easily. To Do this-

The syntax for the REPLACE function in SQL Server (Transact-SQL) is:
REPLACE( string, string_to_replace, replacement_string )

Parameters or Arguments

string : The source string from which a sequence of characters will be replaced by another set of characters.
string_to_replace : The string that will be searched for in string1.
replacement_string : The replacement string. All occurrences of string_to_replace will be replaced with replacement_string in string1.
Note :

The REPLACE function performs a replacement that is not case-sensitive. So all occurrences of string_to_replace will be replaced with replacement_string regardless of the case of string_to_replace or replacement_string

For Example :
SELECT REPLACE('Kapil,raj,chouhan', ',' , ' ') from DUAL;
Result : Kapil raj chouhan

SELECT REPLACE('I Live In India', ' ' , '-') from DUAL;
Result : I-Live-In-India

SELECT REPLACE('facebook.com', 'face' , 'friends') from DUAL;
Result : friendsbook.com

I Hope it will be usefull for you.

Laws