String Tokenizer with Oracle PL/SQL

Published: Monday, 08 December 2014 21:05 | Written by Stefan | Print | Email | Hits: 60116

This article describes how to tokenize a string, just with plain "out of the box" Oracle SQL. In the IT we often encounter requirements to split a string in parts. With Oracle PL/SQL we have a quite elegant solution for that.

The solution is based on the ability of oracle to use regular expressions within a SQL statement. In the first example we have comma seperated string, containing the most important crew members of the USS Enterprise:

SELECT regexp_substr(str, '[^,]+', 1, LEVEL) AS splitted_element,
           LEVEL AS element_no
      FROM (SELECT rownum AS id, 'Kirk,Spock,Scotty,McCoy,Uhura' str FROM dual)
    CONNECT BY instr(str, ',', 1, LEVEL - 1) > 0
           AND id = PRIOR id
           AND PRIOR dbms_random.value IS NOT null;

Now, we develop this base functionality to a general function which splits a string in it's components:

create or replace package itstar_toolbox is

  type t_tab_strings is table of varchar2(1000);
  [...]
  function split_string(pi_string in varchar2, pi_delimiter in varchar2)
    return t_tab_strings is
  
    cursor c_tokenizer(ci_string in varchar2, ci_delimiter in varchar2) is
      SELECT regexp_substr(str, '[^' || ci_delimiter || ']+', 1, LEVEL) AS splitted_element,
             LEVEL AS element_no
        FROM (SELECT rownum AS id, ci_string str FROM dual)
      CONNECT BY instr(str, ci_delimiter, 1, LEVEL - 1) > 0
             AND id = PRIOR id
             AND PRIOR dbms_random.value IS NOT null;
  
    l_tab t_tab_strings := t_tab_strings();
  begin
    for c1 in c_tokenizer(pi_string, pi_delimiter) loop
      l_tab.extend;
      l_tab(l_tab.last) := c1.splitted_element;
    end loop;
  
    return l_tab;
  end;

And now even comes the icing on the cake: Pipelining Function to makes you feel like real SQL:

  function split_string_pipe(pi_string in varchar2, pi_delimiter in varchar2)
    return t_tab_strings pipelined is
    l_tab t_tab_strings;
  begin
    l_tab := split_string(pi_string, pi_delimiter);
    for i in 1..l_tab.count loop
      pipe row (l_tab(i));
    end loop;
    return;
  end;

The result is just great and elegant:

SQL> SELECT * FROM TABLE(itstar_toolbox.split_string_pipe('Kirk,Spock,Scotty,McCoy,Uhura', ',')) a;
 
COLUMN_VALUE
--------------------------------------------------------------------------------
Kirk
Spock
Scotty
McCoy
Uhura
 
SQL>