By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,745 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

Trranslating ORACLE functions to DB2

P: n/a
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;
/

Apr 28 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Apr 28 '06 #2

P: n/a
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.
--

Apr 29 '06 #3

P: n/a
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.
--


May 2 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.