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.).

Digg This!     StumbleUpon     Del.icio.us

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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;
/