Преобразувайте разделен със запетая низ в масив в PL/SQL

Oracle предоставя вградената функция DBMS_UTILITY.COMMA_TO_TABLE.

За съжаление, този не работи с числа:

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

Но с малък трик за префикс на елементите с 'x', той работи:

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.

Поздрави, Роб.

ето още един по-лесен вариант

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

Никога не можем да изчерпим алтернативите да правим едно и също нещо по различен начин, нали? Наскоро открих, че това е доста удобно:

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;

Изходи:

1
2
3

Знам, че Stack Overflow се мръщи на поставянето на URL адреси без обяснения, но тази конкретна страница има няколко наистина добри опции:

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

Особено харесвам този, който преобразува разделения списък във временна таблица, към която можете да изпълнявате заявки:

/* 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.

Прост код

    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;

Да, много е разочароващо, че dbms_utility.comma_to_table поддържа само списъци, разделени със запетая, и то само когато елементите в списъка са валидни PL/SQL идентифицирания (така че числата причиняват грешка).

Създадох общ пакет за анализ, който ще направи това, от което се нуждаете (поставен по-долу). Той е част от моя "demo.zip" файл, хранилище от над 2000 файла, които поддържат моите обучителни материали, всички налични на PL/SQL Obsession: www.toadworld.com/SF.

Поздрави, Steven Feuerstein www.plsqlchallenge.com (ежедневен PL/SQL тест)

    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;
/

Използване на конвейерна таблична функция :

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.

Да видим изхода:

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>

Бързо търсене в моя BBDD ме отведе до функция, наречена 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;

Не знам дали ще е от полза, но го намерихме тук...

Търсех подобно решение, където имах многобайтови знаци (тире, интервал, долна черта) в списъци, разделени със запетая. Така че dbms_utility.comma_to_table не работи за мен.

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;

Това не е експертен отговор, така че се надявам някой да подобри този отговор.

Друга възможност е:

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;

Изпълнението е малко твърде скъпо, тъй като изчислява пълното разделяне всеки път, когато обаждащият се поиска един от елементите там...

Решението, представено по-долу от Стюарт Аштън в тази връзка, е доста удобно. Той елиминира необходимостта списъкът със стойности да бъде цяло число, така че можете да използвате списък с низове.

В клаузата WITH той огражда стойностите с единични кавички, след което я преобразува в таблица с една колона от тип VARCHAR2. https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise/

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

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;
/

така че изберете заявка само в for цикъл може да свърши работа, като замени dosweeklist като разделителен низ и разделител като разделителен знак.

Да видим изхода

a

b

c

Можете да използвате функцията за замяна, за да замените лесно запетаята. Да го направя-

Синтаксисът за функцията REPLACE в SQL Server (Transact-SQL) е:
REPLACE( низ, низ_за_замяна, низ за замяна)

Параметри или аргументи

низ : Изходният низ, от който поредица от знаци ще бъде заменена с друг набор от знаци.
низ_за_замяна : Низът, който ще се търси в низ1.< br> replacement_string : Низът за заместване. Всички срещания на string_to_replace ще бъдат заменени с replacement_string в string1.
Забележка :

Функцията REPLACE извършва замяна, която не е чувствителна към малки и главни букви. Така че всички срещания на string_to_replace ще бъдат заменени с replacement_string, независимо от случая на string_to_replace или replacement_string

Например:
SELECT REPLACE('Kapil,raj,chouhan', ',' , ' ') от DUAL;
Резултат: Kapil raj chouhan

SELECT REPLACE('I Live In India', ' ' , '-') from DUAL;
Резултат : I-Live-In-India

SELECT REPLACE('facebook.com', 'face' , 'friends') от DUAL;
Резултат: friendsbook.com

Надявам се, че ще ви бъде полезно.