How to parse comma delimited string in PL/SQL?
67,740
Solution 1
If you are on Oracle 10G or 11G then you should have a built-in Apex function apex_util.string_to_table
:
SQL> declare
2 v_array apex_application_global.vc_arr2;
3 v_string varchar2(2000);
4 begin
5
6 -- Convert delimited string to array
7 v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ',');
8 for i in 1..v_array.count
9 loop
10 dbms_output.put_line(v_array(i));
11 end loop;
12
13 -- Convert array to delimited string
14 v_string := apex_util.table_to_string(v_array,'|');
15 dbms_output.put_line(v_string);
16 end;
17 /
alpha
beta
gamma
delta
alpha|beta|gamma|delta
PL/SQL procedure successfully completed.
Solution 2
Oracle 11g:
SELECT num_value
FROM ( SELECT TRIM (REGEXP_SUBSTR (num_csv,
'[^,]+',
1,
LEVEL))
num_value
FROM ( SELECT '1,2,3,4,5,6,7,8,9,10' num_csv FROM DUAL)
CONNECT BY LEVEL <= regexp_count (num_csv, ',', 1) + 1)
WHERE num_value IS NOT NULL
Solution 3
It is briefliy , simple example following:
declare
string_to_parse varchar2(2000) := 'abc,def,ghi,klmno,pqrst';
l_count number;
l_value varchar2(2000);
begin
string_to_parse := string_to_parse||',';
l_count := length(string_to_parse) - length(replace(string_to_parse,',',''));
-- In oracle 11g use regexp_count to determine l_count
for i in 1 .. l_count loop
select regexp_substr(string_to_parse,'[^,]+',1,i)
into l_value
from dual;
dbms_output.put_line(l_value);
end loop;
end;
Solution 4
A simple tokenizer SQL statement working for Oracle 10g and 11g could be written as follow:
WITH string AS (
SELECT pv_string value
FROM dual)
SELECT DISTINCT upper(trim(regexp_substr (value, '[^' || pv_separator || ']+', 1, ROWNUM))) value,
level
FROM string
CONNECT BY LEVEL <= LENGTH(regexp_replace (value, '[^' || pv_separator || ']+')) + 1
ORDER BY level;
Where you can replace pv_string
by the string to parse (e.g. 'a,b,c,d,e') and pv_separator
by the separator string (e.g. ',') .
Author by
TERACytE
Updated on December 08, 2020Comments
-
TERACytE over 3 years
I have a comma delimited string in a PL/SQL script (e.g. data:= 'a,b,c,d,e'), that I need to parse out within the script.
I would like to loop through the string and process each item. Like a 'foreach' loop.
Is this possible in PL/SQL? Can someone point me to some code?