Re: split string into an array by some delimiter using 9i?

From: Ken Denny <ken_at_kendenny.com>
Date: Fri, 28 Sep 2007 12:20:27 -0700
Message-ID: <1191007227.090041.158930@k79g2000hse.googlegroups.com>

On Sep 28, 10:56 am, jobs <j..._at_webdos.com> wrote:
> apparently I can't do this in 9i?
>
> myarray := split(sendto, ',');
>
> Any easy way to do it?
>
> Thanks fo rnay help or information.

Here's a function that will do what you want.

FUNCTION split_text(text_ids     IN VARCHAR2
                   ,delim        VARCHAR2
                   ,return_nulls BOOLEAN) RETURN varchar_tab
  PIPELINED IS
  vn_idx         PLS_INTEGER;

  vc_text_ids VARCHAR2(32767) := text_ids;   vc_next_string VARCHAR2(32767);
BEGIN
  LOOP
    vn_idx := instr(vc_text_ids, delim);     IF vn_idx > 0
    THEN

      vc_next_string := substr(vc_text_ids, 1, vn_idx - 1);
      IF vc_next_string IS NOT NULL
         OR return_nulls
      THEN
        PIPE ROW(vc_next_string);
      END IF;
      vc_text_ids := ltrim(substr(vc_text_ids, vn_idx +

length(delim)));

    ELSE

      IF vc_text_ids IS NOT NULL
         OR return_nulls
      THEN
        PIPE ROW(vc_text_ids);
      END IF;
      EXIT;

    END IF;
  END LOOP;
  RETURN;
END split_text; Received on Fri Sep 28 2007 - 14:20:27 CDT