I was in need of tokenizing some comma delimited data within an Oracle Database. A pity, there’s no split for a varchar2 like java.lang.String.split. I could have used Java in the database, but that would be lame, too.j
I found this little function which uses pipelined results, that is, it returns his results while being processed and furthermore, it can be used as a table in a from clause. Great as i need no extra code to check my values in my case.
So here it is:
CREATE OR REPLACE TYPE split_tbl AS TABLE OF VARCHAR2(32767); / show errors; CREATE OR REPLACE FUNCTION f_split ( p_list VARCHAR2, p_del VARCHAR2 := ',' ) RETURN split_tbl pipelined IS l_idx PLS_INTEGER; l_list VARCHAR2(32767) := p_list; l_value VARCHAR2(32767); BEGIN LOOP l_idx := INSTR(l_list,p_del); IF l_idx > 0 THEN pipe ROW(LTRIM(RTRIM(SUBSTR(l_list,1,l_idx-1)))); l_list := SUBSTR(l_list,l_idx+LENGTH(p_del)); ELSE pipe ROW(LTRIM(RTRIM(l_list))); EXIT; END IF; END LOOP; RETURN; END f_split; / show errors;
This thing takes two parameters, the first one the string to be tokenized, the second an optional delimiter. It returns all tokens trimmed as a row for each token.
Kudos to Scott Stephens.
Here is an alternate to the above. I also found this on the internet somewhere.
The difference is the f_split will do everything for you (it will parse the whole string) whereas this one will only fecth one at a time. Might be useful for some 🙂
This is the procedure:
thanks for your input!
I need my f_split versions for selects like:
That is i can use it as join table in arbitrary selects.
Have a great day,
Can you give some example to use with a simple select statement?
DJ: Se my comment above…