Saturday, 21 August 2010

Here are some of my implementation of string splitting function in PL/SQL.
It is something developers need often, and still can't believe there isn't a function that does something like this in oracle 10g...???

Some developers parse delimited text using substr(text,X,Y) for every token they need to use in their code…

My advice is: Don’t ever do this!!! Use some available tokenizer function!

I found several implementations on the internet, but those functions either does not support delimiter with length more than 1, either it does not return tokens that are NULL, or does not return table of varchar2 index by pls_integer ( what I needed), etc.

Conclusion:
There are CERTAINLLY better ways to write this function, and these are just an example how this could be done.

This is one possible approach, to return an array of tokens. For me , probably the better way would be to create an Object that does something like StringTokenizer Java class. Not to return an array, but to provide a way to iterate through tokens using it’s hasMoreTokens() and nextToken() methods.
I think this is better approach if you are dealing with very large input strings and tokens. If you are dealing with small ones, then it’s OK to use one of my funtions I provided here, or any other similar to this one, that you can find on the internet…

If you intend to use one of these, I suggest on using first one, because It is much faster than second one.

I didn’t do any optimizations ( which is obvious  ) because I was pleased with speed. Of course, you are welcome to do it yourself

as a matter of fact, I did some speed testing with input strings whose length is ~ 30000, and the first one was (much) faster than many functions I found on the internet…