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.
views:
154answers:
4
+1
A:
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...
SoulWanderer
2010-09-29 07:07:56
Seems the underlying functions in both our answers are exactly the same :)
Benoit
2010-09-29 07:09:55
Uops! I did not see your answer! It works quite well actually, I have it stored in my Usefull Functions library ;)
SoulWanderer
2010-09-29 08:05:33
+1
A:
Oracle provides a builtin function: dbms_utility.comma_to_table (http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_util.htm#ARPLS73224).
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.
Rob van Wijk
2010-09-30 07:43:33
A:
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;
/