title: Oracle PL/SQL split csv string into n parts
body: <p>I need to split a csv string into n parts. I have addresses in various formats, so I converted them to a csv string. My report requires 3 or 4 address line fields, so I want to split my csv string into the number of parts the report requires.</p>
  
  <p>For example:</p>
  
  <pre><code>10,Dingle Apartments,MANZINI,,MANZINI,NGWANE STREET,,,Swaziland
  </code></pre>
  
  <p>should be split into equal parts for 3 fields:</p>
  
  <pre><code>1:10,Dingle Apartments,MANZINI
  2:,MANZINI,NGWANE STREET
  3:,,,Swaziland
  </code></pre>
  
  <p>I wrote the following code, but it does not work well at all:</p>
  
  <pre><code>-- ============================================================================
  -- This function splits a csv string into x parts and returns one of the parts.
  -- Note: The smallest number of items in a part is hard coded to 2. Needs debug.
  --
  -- p_string        - The csv string.
  -- p_parts         - The number of parts to split into.
  -- p_part          - The splitted part to return.
  -- p_separator     - The separator used in the csv string.
  -- p_separator_out - The separator to return.
  -- p_trim          - Trim trailing separator. Y/N
  -- ============================================================================
  function get_csv_part
  (
     p_string        in varchar2,
     p_parts         in number,
     p_part          in number,
     p_separator     in varchar2,
     p_separator_out in varchar2,
     p_trim          in varchar2 default 'Y'
  )  return varchar2 is
  
  l_answer varchar2(32767) := '';
  l_count  number := 0;
  l_count2 number := 0;
  l_size   number;
  l_pos    number;
  l_pos2   number;
  
  begin
  
     -- hr_utility.trace_on(null, 'FDL');
     hr_utility.trace('p_string: ' || p_string);
  
     l_pos := instr(p_string, p_separator);
  
     -- Determine the number of separators.
     while l_pos &gt; 0 loop
  
        l_count := l_count + 1;
        l_pos := instr(p_string, p_separator, l_pos + 1);
  
     end loop;
  
     -- Get the size of a part.
     if l_count &lt;= p_parts then
        l_size := 2;
     else
        l_size := floor(l_count / p_parts);
     end if;
  
     --if l_size = 1 then
     --   l_size := 2;
     --end if;
  
     hr_utility.trace('l_size: ' || to_char(l_size));
  
     l_pos := instr(p_string, p_separator);
  
     if l_pos = 0 then
        if p_part = 1 then
           l_answer := p_string;
        end if;
     else
        if p_part = 1 then
           l_answer := substr(p_string, 1, l_pos - 1) || p_separator_out;
        end if;
     end if;
  
     -- Split csv into parts.
     while l_pos &gt; 0 loop
  
        l_count2 := l_count2 + 1;
        l_pos2 := instr(p_string, p_separator, l_pos + 1);
  
        hr_utility.trace('----------------------------------------');
        hr_utility.trace('l_count: ' || to_char(l_count));
        hr_utility.trace('l_count2: ' || to_char(l_count2));
        hr_utility.trace('floor(l_count2 / l_size) + 1: ' || to_char(floor(l_count2 / l_size) + 1));
        hr_utility.trace('l_pos: ' || to_char(l_pos));
        hr_utility.trace('l_pos2: ' || to_char(l_pos2));
        hr_utility.trace('l_answer: ' || l_answer);
  
        -- If we are at a position that should go into the returned part.
        if
        (
           l_size &gt; 1
           and floor(l_count2 / l_size) + 1 = p_part
        )
        or
        (
           l_size = 1
           and l_count2 = p_part
        )
        or
        (
           p_part = p_parts
           and l_size = 1
           and l_count2 &gt;= p_part
        )
        or
        (
           p_part = p_parts
           and floor(l_count2 / l_size) + 1 &gt;= p_part
        )  then
           if l_pos2 = 0 then
              if l_pos + 1 &lt; length(p_string) then
                 l_answer := l_answer || substr(p_string, l_pos + 1) || p_separator_out;
              end if;
           elsif ((l_pos + 1) &lt;= (l_pos2 - 1)) then
              l_answer := l_answer || substr(p_string, l_pos + 1, ((l_pos2 - 1) - (l_pos + 1) + 1)) || p_separator_out;
           else
              l_answer := l_answer || p_separator_out;
           end if;
        end if;
  
        l_pos := l_pos2;
  
     end loop;
  
     if p_part = p_parts then
  
        l_pos := instr(p_string, p_separator, 1, l_count);
        -- Dodge.
        if instr(p_string, substr(p_string, l_pos + 1)) = 0 then
           l_answer := l_answer || substr(p_string, l_pos + 1);
        end if;
  
     end if;
  
     if p_trim = 'Y' then
  
        -- Did not work if all separators.
        -- l_answer := trim(trailing p_separator_out from l_answer);
        if substr(l_answer, length(l_answer)) = p_separator_out then
           l_answer := substr(l_answer, 1, length(l_answer) - 1);
        end if;
  
     end if;
  
     return l_answer;
  
  end get_csv_part;
  </code></pre>
  
  <p>Some of the issues are:</p>
  
  <ul>
  <li>It does not work for part size of 1 </li>
  <li>It puts address parts into multiple parts, for example <code>,,TEST,SWAZILIND,LOCATION,THING,,,,</code></li>
  <li>It skips address parts</li>
  <li>It uses unequal part sizes.</li>
  </ul>
  
  <p>Note that for some reason the requirement is to leave the blank fields with separators so a part could be <code>,</code> </p>
  
  <p>Can anyone help me fix this or does anyone have a function that can do this?</p>