I have a string: ,off,on,off

[getjbb]

I have a string:

,off,on,off

I want to parse the data into an array. I found and tweaked (bolded) the following code, but the result was:

<blank line> (think this is coming from the first ',')
off
on
off
---------------------------------------
FUNCTION SPLIT (p_in_string VARCHAR2, p_delim VARCHAR2) RETURN t_array
IS

i number :=0;
pos number :=0;
lv_str varchar2(50) := p_in_string;

strings t_array;

BEGIN

-- determine first chuck of string
pos := instr(lv_str,p_delim,1,1);

-- while there are chunks left, loop
WHILE ( pos != 0) LOOP

-- increment counter
i := i + 1;

-- create array element for chuck of string
strings(i) := substr(lv_str,1,pos-1);

-- remove chunk from string
lv_str := substr(lv_str,pos+1,length(lv_str));

-- determine next chunk
pos := instr(lv_str,p_delim,1,1);

-- no last chunk, add to array
IF pos = 0 THEN

strings(i+1) := lv_str;

END IF;

END LOOP;

-- return array
RETURN strings;

END SPLIT;

-----------------------------------------------

 

[taupirho]

if the leading "," is the problem why not just get rid of
it first before you do any other parsing.

1 declare
2 x varchar2(11) := ',off,on,off';
3 begin
4 dbms_output.put_line(x);
5 x := substr(x,2,length(x)-1);
6 dbms_output.put_line(x);
7* end;
SQL> /
,off,on,off
off,on,off

PL/SQL procedure successfully completed.



In order to understand recursion, you must first understand recursion.

 

[Beilstwh]

Instead of

substr(x,2,length(x)-1);

use

substr(x,2);

Which means start at the second character and go to the end.

Bill
Lead Application Developer
New York State, USA

 

[Dagon]

As I suggested in my response to getjjb's duplicate posting, LTRIM could also be used. I think this would be better because the function would then work regardless of whether there was a leading comma or not.