title: Optimal ORACLE function parsing
body: <p>All,</p>
  
  <p>Whilst I’m not new to SQL it’s been a long long time and I’m very new to Oracle. Please note also I know that the way I have done this is probably the slowest possible solution, but as I said I’m new to this and it worked on the 1000 or so Items I needed to test. The problem is that it DID work so I want to expand this to test 1,000,000’s and I know this is a donkey not a race horse. </p>
  
  <p>Had a small task to “Standardise” a list of company names, so I created a table with known abbreviations</p>
  
  <pre><code>Col1        Col2    Col3    Col4    ….  Col14
  
  Company Co  Cmpy
  Limited     Ltd Lim LMT
  Etc
  </code></pre>
  
  <p>I then wrote a function as to select this table into a cursor, and loop through it for each name sent to it and replace any “NON” standard abbreviations with that in Col 1, using the following:</p>
  
  <p>What I have been thinking about is re-writing the code (obviously) and putting it into a package as I believe that that should result in considerable speed and clarity compared to now.</p>
  
  <p>The problem I have is not really improving the looping part of the function but what I need to do from a Select that calls this function to ensure it loaded the STD_GBR_CO_SUFFIX only once and re-uses the cursor.</p>
  
  <p>I've been reading some of the O'Reilly and I think that what I want to do is possible, I just can't figure out how.</p>
  
  <p>I think I need to put this in a package, and use a REF Cursor, and BULK LOAD looks a contender, but the more I read the more confused I get.
  If someone out there could just could just point me in the right direction I can carry on from there. What I don't want is for someone to just write the solution I want to learn how to do this better not have it done for me.</p>
  
  <p>Thank you all for your assistance in advance.</p>
  
  <p>So if I do a </p>
  
  <pre><code>Select Standardise_Company_Suffix(company_name) AS STD_Company_Name
  From VERY_LARGE_TABLE
  </code></pre>
  
  <hr>
  
  <pre><code>CREATE OR REPLACE Function Standardise_Company_Suffix(Co_Name IN VARCHAR2)
  RETURN varchar2 IS
    stg_Co_Name varchar2(400 byte);
    fmt_S varchar2(20 byte);
    fmt_E varchar2(20 byte);
    parse_Str varchar2(2400 byte);
    parse_char varchar2(4 byte);
  
  CURSOR C1 IS
  -- Populate Cursor with Table of Suffixes
  select * from STD_GBR_CO_SUFFIX;
  
  BEGIN
  
  parse_char := "s";
  
  Fmt_S := '(^|\'||parse_char||')';
  
  Fmt_E := '(\'||parse_char||'|$)';
  
  stg_Co_Name := upper(co_name);
  
  parse_str := ' ';
  
     FOR c1pass IN C1 LOOP
  
     parse_str :='';
  
     If c1pass.column14 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column14)||Fmt_E;   End If;
  
     If c1pass.column13 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column13)||Fmt_E;   End If;
  
     If c1pass.column12 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column12)||Fmt_E;   End If;
  
     If c1pass.column11 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column11)||Fmt_E;   End If;
  
     If c1pass.column10 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column10)||Fmt_E;   End If;
  
     If c1pass.column9 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column9)||Fmt_E;     End If;
  
     If c1pass.column8 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column8)||Fmt_E;     End If;
  
     If c1pass.column7 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column7)||Fmt_E;     End If;
  
     If c1pass.column6 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column6)||Fmt_E;     End If;
  
     If c1pass.column5 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column5)||Fmt_E;     End If;
  
     If c1pass.column4 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column4)||Fmt_E;     End If;
  
     If c1pass.column3 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column3)||Fmt_E;     End If;
  
     If c1pass.column2 is not null then parse_str := parse_str||'|'||Fmt_S||Upper(c1pass.column2)||Fmt_E;     End If;
  
     Parse_str := substr(parse_str,2);
  
     If regexp_instr(stg_Co_Name,parse_str) &lt;&gt; 0 Then
  
              stg_Co_Name := regexp_REPLACE(stg_Co_Name,parse_str,
                              ' $'||UPPER(c1pass.column1||'$ '));
  
     Else
  
              stg_Co_Name := stg_Co_Name;
  
      End if;    
  
   END LOOP;
  
  return stg_Co_Name; 
  
  End;
  
  /
  </code></pre>