Trranslating ORACLE functions to DB2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joost van der Veen - 28 Apr 2006 17:21 GMT

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

Reply to this Message

Serge Rielau - 28 Apr 2006 19:03 GMT

> Hi,
>
[quoted text clipped - 26 lines]
> END list_element;
> /

Show me what you tried and I'll help debug. :-)

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Dave Hughes - 29 Apr 2006 14:08 GMT

> 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,
[quoted text clipped - 11 lines]
> 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.

Reply to this Message

Kiran Nair - 02 May 2006 05:45 GMT

Have you checked this
http://www-128.ibm.com/developerworks/db2/library/samples/db2/0205udfs/index.html

It give sample UDF for migration.
Regards
Kiran Nair

> > Hi,
> >
[quoted text clipped - 52 lines]
> Dave.
> --

Reply to this Message