--------------------------------------------------------
-- Some generic functions and types to work with strings
--------------------------------------------------------

create or replace package str
is

  -- A record for tokenizing strings
  type token_info is record (
    string       varchar2(4000),  -- The string we will be tokenizing
    length       integer,         -- The length of the string
    token_start  integer,         -- Start of last token
    separators    varchar2(4000)  -- Characters which can separate tokens
  );

  -- Return the first token in a string, or null if there are
  -- no more tokens
  function first_token (
    string_in     IN varchar2,
    token_rec_in  IN OUT NOCOPY token_info,
    separators_in IN varchar2 := ' '
  ) return varchar2;

  -- Get the next token in the string, or null if there are 
  -- no more tokens
  function next_token (
    token_rec_in IN OUT NOCOPY token_info
  ) return varchar2;

  -- Determine if a string has more tokens to be returned
  function has_more_tokens (
    token_rec_in IN OUT NOCOPY token_info
  ) return char;

end str;
/
show errors

create or replace package body str 
is

function first_token (
  string_in     IN varchar2,
  token_rec_in  IN OUT NOCOPY token_info,
  separators_IN IN varchar2 := ' '
) return varchar2
is
begin
  
   token_rec_in.string := string_in;
   token_rec_in.length := length(string_in);
   token_rec_in.token_start := 1;
   token_rec_in.separators := separators_in;
   
   return next_token(token_rec_in);
end first_token;


function next_token (
  token_rec_in IN OUT NOCOPY token_info
) return varchar2
is
  v_token_start integer;
begin
  
  -- Check for string end
  if token_rec_in.token_start > token_rec_in.length then
    return null;
  end if;

  -- Skip separators
  while instr(token_rec_in.separators, 
              substr(token_rec_in.string, 
                     token_rec_in.token_start, 1)) <> 0 
  loop

    token_rec_in.token_start := token_rec_in.token_start + 1;
    if token_rec_in.token_start > token_rec_in.length then
      return null;
    end if;

  end loop;

  v_token_start := token_rec_in.token_start;

  -- Skip until the next separator
  while instr(token_rec_in.separators,
              substr(token_rec_in.string, 
                     token_rec_in.token_start, 1)) = 0 
  loop
    token_rec_in.token_start := token_rec_in.token_start + 1;
    if token_rec_in.token_start > token_rec_in.length then
      return substr(token_rec_in.string, v_token_start);
    end if;
  end loop; 
    
  return substr(token_rec_in.string, v_token_start, 
                token_rec_in.token_start - v_token_start);

end next_token;  
  
function has_more_tokens (
  token_rec_in IN OUT NOCOPY token_info
) return char
is
begin
  if token_rec_in.token_start > token_rec_in.length then 
    return 'f';
  else
    return 't';
  end if;
end has_more_tokens;

end str;
/
show errors