String Parsing in a proc/function [message #23327] |
Mon, 02 December 2002 13:08 |
Tony
Messages: 190 Registered: June 2001
|
Senior Member |
|
|
I need to parse a comma delimited string and insert each token into a new table.
For example this is a string which I am passing to a procedure as input parameter:
'ABC,DEF,GHI,JKL,MNO,PQR'
Any help would be appreciated.
thanks
|
|
|
Re: String Parsing in a proc/function [message #23328 is a reply to message #23327] |
Mon, 02 December 2002 13:18 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
The easiest way is to use comma_to_table. It's pretty limited. You'll probably need to use trim() to get rid of leading/trailing spaces.
CREATE OR REPLACE PROCEDURE COMMA_SAMPLE AS
mytable DBMS_UTILITY.uncl_array;
mylist VARCHAR2(80);
mytable_count NUMBER;
BEGIN
mylist := 'Alex, Donna, Hope, Jose, Judy, Julia, Nancy, Paul, Sandy';
DBMS_OUTPUT.PUT_LINE('MYLIST: ' || mylist);
DBMS_UTILITY.COMMA_TO_TABLE(mylist, mytable_count, mytable);
mylist := 'Empty.';
DBMS_OUTPUT.PUT_LINE('MYTABLE: ');
DBMS_OUTPUT.PUT_LINE('---------------------------');
FOR item IN 1..mytable_count LOOP
-- insert in to table...
DBMS_OUTPUT.PUT_LINE(mytable(item));
END LOOP;
DBMS_OUTPUT.PUT_LINE('');
DBMS_UTILITY.TABLE_TO_COMMA(mytable, mytable_count, mylist);
DBMS_OUTPUT.PUT_LINE('MYLIST: ' || mylist);
END;
/
set serveroutput on
begin
comma_sample;
end;
/
|
|
|
|