Q

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

Continued from Part 2...

-- *************************
-- **  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 last published in July 2003

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close