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

User · Accepted Answer

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.

User · Answer

Using a pipelined table function   SQL gt  CREATE OR REPLACE TYPE test type   2  AS   3    TABLE OF VARCHAR2 100    4     Type created   SQL gt  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 gt  SELECT     2  FROM TABLE comma to table  12 3 456     abc def      3     COLUMN VALUE ------------------------------------------------------------------------------ 12 3 456     abc def  8 rows selected   SQL gt

User · Answer

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

User · Answer

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 gt  CREATE TYPE test type AS TABLE OF VARCHAR2 100     2     Type created      Now  create the function     SQL gt  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 gt  SELECT   FROM TABLE f convert  AAA BBB CCC D      COLUMN VALUE -------------------------------------------------------------------------------- AAA BBB CCC D  4 rows selected

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt  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

User · Answer

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  lt   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

User · Answer

here is another easier option  select to number column value  as IDs from xmltable  1 2 3 4 5

User · Answer

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  steven stevenfeuerstein com     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    gt       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      gt  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      gt  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

User · Answer

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  gt  0    select substr input s case when e  gt  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

User · Answer

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

[oracle] Convert comma separated string to array in PL/SQL

Examples related to oracle

Examples related to plsql

Examples related to tokenize