Преобразование разделенной запятой строки в массив в 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. 

Да, очень жаль, что dbms_utility.comma_to_table поддерживает только списки с разделителями-запятыми, и только тогда, когда элементы в списке действительны, PL / SQL идентифицирует (так что номера вызывают ошибку).

Я создал общий пакет синтаксического анализа, который будет делать то, что вам нужно (вставлено ниже). Это часть моего файла «demo.zip», хранилища более 2000 файлов, которые поддерживают мои учебные материалы, все доступные в PL / SQL Obsession: http://www.toadworld.com/SF.

С уважением, Стивен Фейерштайн http://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; / 

Простой код

     создать или заменить функцию get_token (text_is varchar2, token_in number, delim_is varchar2: = ';') return varchar2 is
        text_ls varchar2 (2000);
        spos_ln number;
        epos _ln number;
     начать
        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;

Использование функции конвейерной таблицы :

 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; 

Это не экспертный ответ, поэтому я надеюсь, что кто-то улучшит этот ответ.

 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 eg 1,2,,,,5,6,7 l_x := l_x + 1; END LOOP; END IF; RETURN l_strlist; END string_to_list; 

Другая возможность:

 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 es else length(input) end) into nthVal from candidates where n=nth; return nthVal; END getNth; 

Это слишком дорого для запуска, так как он вычисляет полный раскол каждый раз, когда вызывающий абонент запрашивает один из предметов там …

Вы можете использовать функцию замены, чтобы легко заменить запятую. Сделать это-

Синтаксис функции REPLACE в SQL Server (Transact-SQL):
REPLACE (строка, string_to_replace, replacement_string)

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

string: Исходная строка, из которой последовательность символов будет заменена другим набором символов.
string_to_replace: Строка, которая будет искать в строке1.
replacement_string: строка замены. Все вхождения string_to_replace будут заменены на replace_string в string1.
Примечание .

Функция REPLACE выполняет замену, которая не учитывает регистр. Таким образом, все вхождения string_to_replace будут заменены на replace_string независимо от случая string_to_replace или replace_string

Например :
SELECT REPLACE ( ‘Kapil, raj, chouhan’, ‘,’, ” ) из DUAL;
Результат: Kapil raj chouhan

SELECT REPLACE ( «Я живу в Индии», «», «-» ) от DUAL;
Результат: I-Live-In-India

SELECT REPLACE ( «facebook.com», «лицо», «друзья» ) из DUAL;
Результат: friendsbook.com

Надеюсь, это будет полезно для вас.

 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; 

 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