Requires Free Membership to View
-- *************************
-- ** n e x t _ p a r t **
-- *************************
-- next_part returns the beginning portion of in_out_text,
-- up to (but not including) the first unquoted occurrence
-- of in_sep_text. When this function returns, all of
-- in_out_text through the first unquoted occurrence of
-- in_sep_text (inclusive) will be removed. If in_out_text
-- begins with in_quote_text, then the returned value will
-- include everything up to (but not including) the next
-- single occurrence of in_quote_text (possibly including
-- in_sep_text), with doubled occurrences of in_quote_text
-- replaced by single ones.
-- Example 1: after this code
-- s := '"This "","" is not a separator", or is it?';
-- x := next_part (s, ',', '"')
-- x is equal to 'This "," is not a separator', and s is ' or is it?'.
-- Example 2: after this code
-- s := '"This "","" is not a separator": or is it?,Blah, blah';
-- x := next_part (s, ',', '"')
-- x is equal to 'This "," is not a separator', and s is 'Blah, blah'.
-- The unclaimed text between the closing quote and the next
-- separator (': or is it?') is stored in a package variable, which
-- you can retrieve by calling get_unclaimed_text.
-- Note that if the first segment contains in_quote_text, but
-- does not start with it, then the quotes will not be
-- treated specially.
-- in_sep_text and in_quote_text may be longer than one character.
FUNCTION next_part
(
in_out_text IN OUT VARCHAR2,
in_sep_text IN VARCHAR2 -- separator
DEFAULT ',',
in_quote_text IN VARCHAR2 -- enclosing string
DEFAULT NULL
)
RETURN VARCHAR2
IS
quote_len PLS_INTEGER := LENGTH (in_quote_text);
quote_pos PLS_INTEGER; -- position of enclosing string
return_text VARCHAR2 (32767);
sep_len PLS_INTEGER := LENGTH (in_sep_text);
sep_pos PLS_INTEGER; -- position of separator
BEGIN
IF in_out_text IS NULL
THEN -- NULL input, NULL output
return_text := '';
ELSIF INSTR (in_out_text, in_quote_text) = 1
THEN -- it begins with quote
quote_pos := end_quote_pos ( in_out_text,
in_quote_text
);
return_text := unquote ( SUBSTR ( in_out_text,
1,
quote_pos + quote_len - 1
),
in_quote_text
);
in_out_text := SUBSTR (in_out_text,
quote_len + quote_pos
);
-- deal with unclaimed text following closing quote
unclaimed_text := '';
sep_pos := INSTR (in_out_text, in_sep_text);
IF sep_pos = 1
THEN -- no stray text, discard separator
in_out_text := SUBSTR (in_out_text, 1 + sep_len);
ELSE -- there was stray text
unclaimed_text := SUBSTR ( in_out_text,
1,
sep_pos - 1
);
in_out_text := SUBSTR ( in_out_text,
sep_pos + sep_len
);
END IF;
ELSE -- nothing special
sep_pos := INSTR (in_out_text, in_sep_text);
IF sep_pos = 0
THEN -- No separator; return whole string
return_text := in_out_text;
in_out_text := '';
ELSE -- Return part before separator
return_text := SUBSTR ( in_out_text,
1,
sep_pos - 1
);
in_out_text := SUBSTR ( in_out_text,
sep_pos + 1
);
END IF;
END IF;
RETURN return_text;
END next_part;
Continued on the next page...
This was first published in July 2003
Join the conversationComment
Share
Comments
Results
Contribute to the conversation