Parse List of Author Names

The following code was used to bulk parse a set of author names in the form of:

firstname [middlename] lastname, [firstname middlename lastname] ...

Prerequisites:

An APEX collection created with the name of AUTHORS
An APEX textarea item with the name Pnnn_AUTHOR_STRING.
A button with request name PARSE_AUTHORS

The author names are parsed and added as members to an APEX collection. This was designed for a specific purpose but the code may be able to modified to fit different situations.

Step 1: create an APEX item called Pnn_AUTHOR_STRING as textarea.

Step 2: Add a button called PARSE_AUTHORS

Step 3: Add a submit process that executes when the button is pressed with the following code:

DECLARE
 
l_rank number;
l_str varchar2(4000);
 
CURSOR STR IS
select 
  trim(regexp_substr(name, '^[^ ]*')) first,
  trim(regexp_substr(name, ' .*[ ]')) middle,
  trim(regexp_substr(name, '[^ .]*$')) last
   --dump(name) first, null middle, null last
from
  (select trim(regexp_substr(l_str,'[^,]+', 1, level)) name from dual
   connect by regexp_substr(l_str, '[^,]+', 1, level) is not null);
 
BEGIN
 
 SELECT NVL(MAX(TO_NUMBER(C002))+1,1) INTO l_rank
   FROM APEX_COLLECTIONS
   WHERE COLLECTION_NAME = 'AUTHORS';
 
  -- Take out carraige returns, replace with space
  l_str := :P29_AUTHOR_STRING;
  l_str := replace(:P29_AUTHOR_STRING,chr(13) || chr(10),chr(32));
 
  -- temporarily change that crazy character to something we know
  l_str := replace(l_str,chr(164),'!'); 
 
  -- strip out the superscript letters
  l_str := regexp_replace(l_str,'! *[a-z]*','');
 
  -- remove any special characters
  l_str := regexp_replace(l_str,'[^a-zA-Z,. ]','',1,0,'i');
 
  -- trim returns
  l_str := trim(chr(32) from l_str); 
 
  -- remove multiple spaces
  l_str := regexp_replace(l_str, '( ){2,}','\1' );
 
  l_str := trim(l_str); 
 
  FOR C1 IN STR LOOP
 
  APEX_COLLECTION.ADD_MEMBER(p_collection_name => 'AUTHORS',
      p_c001          => 'NEW',
      p_c002          => l_rank,
      p_c003          => NULL,
      p_c004          => 
      trim(C1.LAST || ', ' ||
      C1.FIRST || ' ' ||
      C1.MIDDLE),
      p_c005          => 'AUTHOR',
      p_c006          => NULL,
      p_c007          => C1.FIRST,
      p_c008          => C1.MIDDLE,
      p_c009          => C1.LAST,
      p_c010          => NULL,
      p_c011          => NULL,
      p_generate_md5 => 'YES');
  l_rank := l_rank + 1;
 
 
  END LOOP; 
 
END;

Optionally, while testing, you might want to create a classic report based on the collection.