Joost van der Veen wrote:

> Hi,
>
> can somebody help me to translate this oracle function to a DB2
> function;
> I've tried to do this but it didn't work.

As Serge mentioned, a example of what you tried and the resulting error
would be helpful...

> With this function i can separate one textfield with a delimmeter in
> the text, in multiple colums without export to Excel.

If this is what you're looking to do I strongly recommend taking a look
at the article mentioned a post or two back, Knut's Parsing Strings in
SQL (http://tinyurl.com/k4tuv).

The functions in the article use recursion instead of iteration to
separate out the elements of the source string, so you might find it
rather "alien" compared to the iterative approach you've used below.

> CREATE FUNCTION list_element
> (p_string VARCHAR2,
> p_element INTEGER,
> p_separator VARCHAR2)
> RETURN VARCHAR2
> AS
> v_string VARCHAR2(32767);
> BEGIN
> v_string := p_string || p_separator;
> FOR i IN 1 .. p_element - 1 LOOP
> v_string := SUBSTR(v_string,INSTR(v_string,p_separator)+1);
> END LOOP;
> RETURN SUBSTR(v_string,1,INSTR(v_string,p_separator)-1);
> END list_element;
> /

Actually, there's a minor error in this original definition: assuming
p_separator can be more than one character, the third line after BEING
should be:

v_string :=
SUBSTR(v_string,INSTR(v_string,p_separator)+LEN(p_ separator));

Or LENGTH instead of LEN, or whatever the Oracle function for measuring
the length of a VARCHAR is.

If you still want to convert this to DB2 after reading Knut's article I
recommend looking at the LOCATE function (don't bother with POSSTR as
it can only use constants for the search-string), and use a WHILE loop
(the FOR loop in DB2 is a different beast entirely ... it's for
iterating over result sets, not ranges of numbers).


HTH,

Dave.
--