Re: INSTR challenge

From: Matt B. <mcb_at_ds.znet.com>
Date: Wed, 21 Feb 2001 20:49:54 -0800
Message-ID: <t996d4hgbjiv7a_at_corp.supernews.com>

"Jenny Farnham" <farnham_at_spot.colorado.edu> wrote in message news:971gjg$885$1_at_peabody.colorado.edu...
>
> I have a character field that holds something like
> this:
>
> 12345,678,123
>
> The comma is like a delimeter.
>
> So, ultimately I'd like to pull out
>
> 12345 and 678 and 123
>
> I know INSTR will return the location of the commas and
> I could use sustr to help, but how do I get all 3 numbers
> to be pulled out?
>
> Thanks,
> Jen

PROCEDURE READ_MY_LIST(p_list_io IN OUT VARCHAR2,p_temp_io OUT VARCHAR2) IS

  v_length NUMBER := 0;
  v_first_comma NUMBER := 0;
  v_second_comma NUMBER := 0;

BEGIN
  v_first_comma := INSTR(p_list_io,',',1);   v_second_comma := INSTR(p_list_io,',',1,2);   IF v_first_comma = 0 OR v_first_comma IS NULL THEN

    --
    -- Only one item in the list
    -- (e.g.: 'Hello')
    --

    p_temp_io := p_list_io;
    p_list_io := NULL;
  ELSIF v_second_comma > 0 AND
    v_second_comma = v_first_comma + 1 THEN

    --
    -- NULL in the middle of the list
    -- (e.g.: 'Hello,,goodbye')
    -- Might as well just remove the null while we're at
    -- it and get through the list faster...?
    --
    v_length := v_first_comma - 1;

    p_temp_io := SUBSTR(p_list_io,1,v_length);     p_list_io := SUBSTR(p_list_io,v_second_comma+1)   ELSIF v_second_comma > 0 THEN

    --
    -- Typical list
    -- (e.g.: 'Hello,you are hot!,goodbye')
    --
    v_length := v_first_comma - 1;

    p_temp_io := SUBSTR(p_list_io,1,v_length);     p_list_io := SUBSTR(p_list_io,v_first_comma+1);   END IF;

  --
  -- If you hate trailing and leading spaces like I do
  -- then trim them off.
  --

  p_temp_io := LTRIM(RTRIM(p_temp_io));
END;
/

Now call/use the procedure:

DECLARE
  v_list VARCHAR2(32767):= '12345,678,123';   v_temp VARCHAR2(32767):= NULL;

  --
  -- Use whatever variable size works for you
  -- 32767 is the max for a VARCHAR2 in PL/SQL
  --

BEGIN
  WHILE v_list IS NOT NULL LOOP
    READ_MY_LIST(v_list,v_temp);

    --
    -- Do stuff with v_temp here
    -- Every iteration of the loop will remove the first item
    -- from your list and place it in v_temp until the list
    -- is empty.
    --

  END LOOP;
END;
/

I don't have access to SQL and PL/SQL right now to check it for sure but give it a try. Might need to make a few adjustments but you get the idea.

-Matt Received on Thu Feb 22 2001 - 05:49:54 CET