split_string in PL/SQL

Sometimes, if you’re used to writing code in other (imperative or functional) 3GL languages, PL/SQL can be a strange world.
Little things that were easy to do elsewhere suddenly become difficult. It should be straightforward to split a string into its components, shouldn’t it? In functional languages like Haskell or Scheme, a string is just a list of characters, in Java, there’s String.split…

In PL/SQL, there’s no split function, but at least there are utilities to make use of (sorry for the pun, but really alluding to dbms_utility here ;-))

In fact, the string to character array conversion can be done using dbms_utility.comma_to_table, with the caveat that this procedure accepts identifiers only. Fortunately, this can be circumvented by temporarily surrounding each single token with double quotes.
Then all that remains is interleaving the initial string’s characters with commas (stripping the last), and there you have a convenience function, split_string:

create or replace function split_string(p_string in varchar2) return dbms_utility.uncl_array is
  l_string  varchar2(4000);
  l_tablen  binary_integer;
  l_chartab dbms_utility.uncl_array;
  l_string := regexp_replace(regexp_replace(p_string,
  dbms_utility.comma_to_table(l_string, l_tablen, l_chartab);
  for i in l_chartab.first..l_chartab.last-1 loop
    l_chartab(i) := regexp_replace(l_chartab(i), '"(.)"', '\1');
  end loop;
  return l_chartab;
end split_string;

And here’s a test using different kinds of strings:

set serveroutput on
  type stringtab_t is table of varchar2(32);
  l_stringtab stringtab_t := stringtab_t('Lillehammer', '777', '& $!/)');
  l_chartab dbms_utility.uncl_array;
  for i in l_stringtab.first .. l_stringtab.last loop
    l_chartab := split_string(l_stringtab(i));
    dbms_output.put_line(chr(10) || l_stringtab(i));
    for j in l_chartab.first..l_chartab.last-1 loop
      dbms_output.put_line('|' || l_chartab(j) || '|');
    end loop;
  end loop;



& $!/)
| |

PL/SQL procedure successfully completed.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s