Ora | getNext

Get_Next

This function is useful for extracting the values from a comma separated values.

Parameters

pv_in_string	is the delmited string
pn_in_pos	position of the value that is required
pv_in_delimiter	single delimiter char
pv_in_endofstr	If this string is set to 'Y' then it will return a string like $$END$$ when the    requested position(pn_in_no) is greater than the maximum position available    within that string. i.e if the position is beyond the end of the string. This might    be useful to decide when to EXIT when the we are writing a LOOP logic on a    comma delimted string. Normally we exit when we get a NULL value for a    position.

Function

create or replace
CREATE OR REPLACE FUNCTION VSUBR.getNext( pv_in_string     IN VARCHAR2,
                  pn_in_pos         IN NUMBER,
                  pv_in_delimiter  IN VARCHAR2 DEFAULT ',',
                  pv_in_endofstr   IN VARCHAR2 DEFAULT 'N' )
RETURN VARCHAR2 Is

 lv_result          VARCHAR2(32000);
 lv_temp_str        VARCHAR2(32000);BEGIN
  ---- add 1 , to the string, so that the last value will be returned.
  IF pn_in_pos  > ( NVL( LENGTH(pv_in_string),0) - LENGTH( NVL( REPLACE( pv_in_string,pv_in_delimiter), 0)) +1) THEN
     IF pv_in_endofstr = 'Y' THEN
        RETURN '$$END$$';
     ELSE
        RETURN NULL;
     END IF;
  ELSIF pn_in_pos <=0 THEN
    RETURN NULL;
  END IF;
  lv_temp_str := pv_in_string || pv_in_delimiter; 
  lv_result   := RTRIM( REGEXP_SUBSTR( lv_temp_str, '[^,]{0,},', 1, pn_in_pos), ',');

  RETURN lv_result;
END getNext;