How to parse comma delimited string in PL/SQL?

Question

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?

Have a look at @TonyAndrews blog: http://tonyandrews.blogspot.com/2004/10/parsing-delimited-fields-in-character.html — StevieG, Aug 11 '11 at 14:58
StevieG, that worked. Please add it as the answer to my question so I can accept it and vote it up. — TERACytE, Aug 11 '11 at 15:27
There is another technique outlined here http://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement basically using regexp_substr. — Greg Reynolds, Aug 11 '11 at 15:38

score 16 · Answer 1 · answered Aug 11 '11 at 16:01

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.

It seems 11G doesn't always have this built in. I get an error telling me apex_application_global.vc_arr2 must be defined. — Adam, Aug 13 '15 at 14:39
@Adam It may be called `htmldb_application_global` in older versions. — Tony Andrews, Aug 13 '15 at 17:09

score 4 · Answer 2 · edited Sep 25 '14 at 06:11

4

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

edited Sep 25 '14 at 06:11

sp_m

2,647
8
38
62

answered Sep 25 '14 at 05:45

Julfcar Ali

41
1

This even works on Oracle 10. – mvermand Oct 21 '15 at 15:47
@mvermand regexp_count is not present in 10g – jareeq Jun 15 '16 at 11:35

score 3 · Answer 3 · answered Aug 02 '16 at 10:20

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;

Guillaume · Answer 4 · 2017-12-07T22:17:33.560

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. ',') .

score 0 · Answer 5 · edited Jul 02 '15 at 16:59

For a basic comma delimited string. execute procedure below it is designed to wittle the record down a piece at a time. When p_input_string = p_output_value it is done. Default is comma but you can pass in a different delimiter
parse_delimited_string(V_string, v_value, ';'); --- semicolon delimited

create or replace procedure parse_delimited_string(P_INPUT_STRING IN OUT VARCHAR2,
                                               P_OUTPUT_VALUE OUT VARCHAR2,
                                               P_DELIMITOR IN VARCHAR2 DEFAULT ',') 
is

 /*This Procedure will parse out the first field of a delimited string it will return
 the result and a the orginal string minus the parsed out string.             
 the ideal would be to execute this procedure for each field you want to extract 
 from string. If you don't know how many values you need to parse out you can just
 keep executing this until the p_input_strng equals the P_output_value
 */

begin

IF (instr(P_INPUT_STRING, P_DELIMITOR)) > 0
  THEN
    P_OUTPUT_VALUE := substr(P_INPUT_STRING, 1, (instr(P_INPUT_STRING, P_DELIMITOR)));  
    P_INPUT_STRING := regexp_replace(P_INPUT_STRING, P_OUTPUT_VALUE, '',1,1);
    P_OUTPUT_VALUE := replace(P_OUTPUT_VALUE, P_DELIMITOR, '');
    IF NVL(P_INPUT_STRING, ' ') = ' '
      THEN
        P_INPUT_STRING := P_OUTPUT_VALUE;
    END IF;    

  ELSE
    P_OUTPUT_VALUE := P_INPUT_STRING;
  END IF;

end parse_delimited_string;

score -1 · Answer 6 · edited Nov 28 '13 at 07:04

-1

declare

type vartype is varray(10) of number;

x1 vartype;

total integer;

begin
x1 := vartype (1,2,3,4);

total := x1.count;

for i in 1 .. total  loop 

dbms_output.put_line(x1(i));        

end loop;

end;


/

edited Nov 28 '13 at 07:04

Baby Groot

4,637
39
52
71

answered Nov 28 '13 at 06:45

Ashish sinha

148
2
9

This can be done with the help of collections (varray and table). – Ashish sinha Nov 28 '13 at 06:46
It works, except for the purpose of needing a string to be parsed from a varchar to the varray. The purpose of the question, as I understand it, is to take a string and get it into a format that can be used, such as the varray. This answer is missing the portion to get it from the string to the collection. – Laura Ritchey Jan 15 '15 at 14:20

How to parse comma delimited string in PL/SQL?

6 Answers6

Related