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:
1 | SELECT regexp_substr(str, '[^,]+' , 1, LEVEL ) AS splitted_element, |
3 | FROM ( SELECT rownum AS id, 'Kirk,Spock,Scotty,McCoy,Uhura' str FROM dual) |
4 | CONNECT BY instr(str, ',' , 1, LEVEL - 1) > 0 |
6 | 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:
01 | create or replace package itstar_toolbox is |
03 | type t_tab_strings is table of varchar2(1000); |
05 | function split_string(pi_string in varchar2, pi_delimiter in varchar2) |
06 | return t_tab_strings is |
08 | cursor c_tokenizer(ci_string in varchar2, ci_delimiter in varchar2) is |
09 | SELECT regexp_substr(str, '[^' || ci_delimiter || ']+' , 1, LEVEL ) AS splitted_element, |
11 | FROM ( SELECT rownum AS id, ci_string str FROM dual) |
12 | CONNECT BY instr(str, ci_delimiter, 1, LEVEL - 1) > 0 |
14 | AND PRIOR dbms_random.value IS NOT null ; |
16 | l_tab t_tab_strings := t_tab_strings(); |
18 | for c1 in c_tokenizer(pi_string, pi_delimiter) loop |
20 | l_tab(l_tab. last ) := c1.splitted_element; |
And now even comes the icing on the cake: Pipelining Function to makes you feel like real SQL:
01 | function split_string_pipe(pi_string in varchar2, pi_delimiter in varchar2) |
02 | return t_tab_strings pipelined is |
05 | l_tab := split_string(pi_string, pi_delimiter); |
06 | for i in 1..l_tab. count loop |
The result is just great and elegant: