Konvertiert eine durch Kommas getrennte Zeichenfolge in PL / SQL in ein Array

Wie konvertiere ich eine durch Kommas getrennte Zeichenfolge in ein Array?

Ich habe die Eingabe ‘ 1,2,3' , und ich muss es in ein Array konvertieren.

Oracle stellt die eingebaute function DBMS_UTILITY.COMMA_TO_TABLE zur Verfügung .

Leider funktioniert dieser nicht mit Zahlen:

 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 

Aber mit einem kleinen Trick, um die Elemente mit einem ‘x’ voranzubringen, funktioniert es:

 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. 

Grüße, Rob.

Hier ist eine weitere einfachere Option

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

Wir können niemals die Alternativen auslassen, das Gleiche anders zu machen, oder? Ich fand vor kurzem, dass das ziemlich praktisch ist:

 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; 

Ausgänge:

 1 2 3 

Ich weiß, dass Stack Overflow beim Einfügen von URLs ohne Erläuterungen runzelt, aber diese Seite hat ein paar wirklich gute Optionen:

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

Ich mag besonders diese, die die begrenzte Liste in eine temporäre Tabelle konvertiert, gegen die Sie Abfragen ausführen können:

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

Ja, es ist sehr frustrierend, dass dbms_utility.comma_to_table nur durch Kommas getrennte Listen unterstützt und nur dann, wenn Elemente in der Liste gültige PL / SQL-Identifizierungen sind (so dass Zahlen einen Fehler verursachen).

Ich habe ein generisches Parsing-Paket erstellt, das genau das tut, was Sie brauchen (unten eingefügt). Es ist Teil meiner Datei “demo.zip”, einem Repository mit über 2000 Dateien, die meine Schulungsmaterialien unterstützen, die alle unter PL / SQL Obsession verfügbar sind: http://www.toadworld.com/SF.

Grüße, Steven Feuerstein http://www.plsqlchallenge.com (tägliches 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 ('> ' || 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; / 

Einfacher Code

     Erstelle oder ersetze die function get_token (text_is varchar2, token_inzahl, delim_is varchar2: = ';') return varchar2 is
        text_ls varchar2 (2000);
        spos_ln Nummer;
        epos _ln Nummer;
     Start
        text_ls: = Trennzeichen ||  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;

Verwenden einer Pipeline-Tabellenfunktion :

 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. 

Lassen Sie uns die Ausgabe sehen :

 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> 

Eine schnelle Suche auf meinem BBDD brachte mich zu einer function namens 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; 

Ich weiß nicht, ob es nützlich sein wird, aber wir haben es hier gefunden …

Ich suchte nach einer ähnlichen Lösung, bei der ich Multibyte-Zeichen (Bindestrich, Leerzeichen, Unterstrich) in durch Kommas getrennten Listen hatte. Also dbms_utility.comma_to_table funktionierte nicht für mich.

 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; 

Dies ist keine Expertenantwort, also hoffe ich, dass jemand diese Antwort verbessern würde.

 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; 

Eine andere Möglichkeit ist:

 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; 

Es ist ein wenig zu teuer, um zu laufen, da es die komplette Aufteilung jedes Mal berechnet, wenn der Anrufer nach einem der Gegenstände dort fragt …

Sie können die function ersetzen verwenden, um das Komma einfach zu ersetzen. Um dies zu tun-

Die Syntax für die REPLACE-function in SQL Server (Transact-SQL) lautet:
REPLACE (string, string_to_replace, ersatz_string)

Parameter oder Argumente

string: Die Quellzeichenfolge, aus der eine Zeichenfolge durch eine andere Zeichenfolge ersetzt wird.
string_to_replace: Die Zeichenfolge, nach der in string1 gesucht wird.
replacement_string: Die Ersetzungszeichenfolge. Alle Vorkommen von string_to_replace werden durch string1 ersetzt.
Hinweis :

Die REPLACE-function führt einen Austausch durch, bei dem die Groß- / Kleinschreibung nicht berücksichtigt wird. Daher werden alle Vorkommen von string_to_replace durch replacement_string ersetzt, unabhängig vom Fall von string_to_replace oder ersatz_string

Beispielsweise :
SELECT REPLACE ( ‘Kapil, Raj, Chouhan’, ‘,’, ” ) von DUAL;
Ergebnis: Kapil Raj Chouhan

SELECT REPLACE ( ‘Ich lebe in Indien’, ”, ‘-‘ ) von DUAL;
Ergebnis: I-Live-In-Indien

SELECT REPLACE ( ‘facebook.com’, ‘Gesicht’, ‘Freunde’ ) von DUAL;
Ergebnis: freundesbuch.com

Ich hoffe, es wird für Sie nützlich sein.

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

So wählen Sie Abfrage nur in for-Schleife kann den Trick tun, indem Sie dosweeklist als Trennzeichen Zeichenfolge und Trennzeichen als Begrenzungszeichen ersetzen.

Sehen wir uns die Ausgabe an

 a b c