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.

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

Please Help!

Kind Regards,

Joost

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;
/

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.
>
> With this function i can separate one textfield with a delimmeter in
> the text, in multiple colums without export to Excel.
>
> Please Help!
>
> Kind Regards,
>
> Joost
>
> 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;
> /
>
Show me what you tried and I'll help debug. :-)

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

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.
--

Have you checked this
http://www-128.ibm.com/developerwork...dfs/index.html

It give sample UDF for migration.
Regards
Kiran Nair

Dave Hughes wrote:

> 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.
> --