Splitting a string, part 2

 01|03|07|11|99|100|231

I need to split the string in the procedure, then select from table a a value based on the values passed in from the string, and then I need to insert that value into table b. I want to do this loop for every value contained in the string ('01' first, then '03', '07', etc.).

Continued from page 1.

Here's one way of implementing a "divide" function using INSTR and SUBSTR. The INSTR function is useful for locating a substring (like '|') within a larger string. The SUBSTR function is used to retrieve parts of a string, based on their position within it.

CREATE OR REPLACE  FUNCTION  first
(
    inout_text         IN OUT  VARCHAR2,        -- Text to be parsed
    in_separator_text  IN      VARCHAR2 := '|'  -- between entries
)
RETURN  VARCHAR2

--      *****************
--      **  f i r s t  **
--      *****************

--  first returns the portion of inout_text before the first
--  occurrence of in_separator_text (which can be a single character 
--  or a longer string).  The value returned and the first occurrence 
--  of in_separator_text are removed from inout_text, so you can call 
--  it repeatedly like this:

--    WHILE  arg_text  IS NOT NULL
--    LOOP
--        this_text := first (arg_text);
--        IF  this_text  IS NOT NULL
--        THEN
--            ...
--        END IF;
--    END LOOP;

--  If arg_text is set to 'a|bc||defg' before entering the loop above,
--  PL/SQL will execute the loop four times (with this_text set to
--  'a', 'bc', NULL and 'defg').

IS
    return_text           VARCHAR2 (4000);
    separator_length_val  PLS_INTEGER := LENGTH (in_separator_text);
    separator_pos_val     PLS_INTEGER;
BEGIN
    IF inout_text  IS NULL
    THEN
         return_text := '';
    ELSE
        separator_pos_val := INSTR ( inout_text,
                                     in_separator_text
                                   );

        IF  separator_pos_val = 0
        THEN  -- No separator, entire text is one entry
            return_text := inout_text;
            inout_text := '';
        ELSIF  separator_pos_val = 1
        THEN  -- Empty entry
            return_text := inout_text;
            inout_text := SUBSTR ( inout_text,
                                   separator_length_val + 1
                                   -- no 3rd arg, SUBSTR returns
                                   --   the rest of the string
                                 );
        ELSE  -- Entry followed by separator
            return_text := SUBSTR ( inout_text, 
                                    1, 
                                    separator_pos_val - 1
                                  );
            inout_text := SUBSTR ( inout_text,
                                   separator_pos_val +
                                       separator_length_val
                                 );
        END IF;
    END IF;

    RETURN  return_text;
END   first;
/

Once you have these two more or less generic routines, you can write a single routine that does exactly what you want by combining them:

CREATE OR REPLACE PROCEDURE  first_and_goal
(
    in_text            IN  VARCHAR2,        -- Text to be parsed
    in_separator_text  IN  VARCHAR2 := '|'  -- between entries
)

--  ***********************************
--  **  f i r s t _ a n d _ g o a l  **
--  ***********************************

--  first_and_goal calls goal for each item in in_text, where the items
--  are separated by in_separator_text (default "|").

IS
    shrinking_text  VARCHAR2 (32000) := in_text;
    this_text       VARCHAR2 (32000);
BEGIN
    WHILE  shrinking_text  IS NOT NULL
    LOOP
        this_text := first ( shrinking_text,
                             in_separator_text
                           );

        IF  this_text  IS NOT NULL
        THEN
            goal (this_text);
        END IF;
    END LOOP;
END    first_and_goal;
/