Ask the Expert

Using PL/SQL to read delimited ASCII text files... Part 3

Continued from Part 2...

    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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: