Recently I needed to split a delimited string with PL/SQL. I just did not want to write the xx version of a "parse delimited string" package, and so I stumbled upon Alex Nuijtens Blog. This solution was not the least complex, but so beautiful I almost wanted to cry ;-). !Thanks for sharing your solution at this point Alex! It worked perfectly and so I implemented it in my programs. After a few more tests with larger string (~5000-10000) I noticed huge performance issues. So I did a fast check with using a "normal" loop and not the hierachic query Alex is using. Same result, terribly slow (~30s). After thinking a bit (...happens from time to time...) it was clear, normally if you parse a delimited string, you always cut the part off the front, that you parsed and put it somewhere else (collection....), what did not happen by using this solution. So I rewrote the pure SQL approach to a PL/SQL function. So The code looks now:
vParseString := pParseText;
FOR i IN 1..length (regexp_replace (vParseString,vRegExp)) + 1
LOOP
vToken := REGEXP_SUBSTR (vParseString,vRegExp);
vCutPos := REGEXP_INSTR(vParseString,vRegExp,1,2);
vParseString := substr(vParseString,vCutPos);
DBMS_OUTPUT.PUT_LINE(vToken);
EXIT WHEN vCutPos = 0;
END LOOP;
The complete programs needs below 0.1 seconds to finish in comparison to 30 before.
So Alex program is good for small strings, but does not fit my needs in this case.
Probably it's much faster if you use analytic functions, where you always replace the string of the previous line...
I think if I have some time I will give that a try, because a pure SQL solution would be much more beautiful than a PL/SQL approach ;-).