• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • Last Modified:

DB2 - How to create a function that takes dynamic input

I am trying to write a function in DB2 and the function will parse a certain string based on delimiter and return as seperate fields .
The string I should pass will come from another query ,

example :
  RETURNS TABLE or row  ( string1 VARCHAR(100),string2 VARCHAR(100))
   RETURN values (tempstring,tempstring1);--

Now can I call this function and pass a set of strings like select somestring from table ? or use the same query select somestring from table in the function and iterate through it ?
  • 2
1 Solution
Kent OlsenData Warehouse Architect / DBACommented:
Hi Franklin,

If you want to return a known number items, it's pretty easy.  

  RETURNS TABLE or row  ( string1 VARCHAR(100),string2 VARCHAR(100))
      case when locate(';', string) = 0 then string else left (string, locate(';',string)-1) end Leftside,
      case when locate(';', string) = 0 then ' ' else substr (string, locate (';', string)+1) end Rightside
    FROM sysibm.sysdummy1;

Open in new window

The string "AB;CDEF" is returned as "AB" and "CDEF".
The string "ABCDEF" is returned as "ABCDEF" and "".

It's a little redundant, but the left and right half of the string are derived separately and both processes need to ensure that the separator character exists in the string.  Otherwise, locate() will return 0 which is an illegal value when passed to LEFT or SUBSTR.

FranklinRaj22Author Commented:

Thanks for the response . I believe I did not pose the right question , the input parameter should be a list or use a query and store it in an array and iterate over .

example : calling the function
select * from table SPLIT_STRING(select stringname from table where condition);

or I can use the same query inside the function and do a
SET ARRAY VARCHAR = select stringname from table where condition ;

Ideally my input should be a collection not scalar .
Kent OlsenData Warehouse Architect / DBACommented:
I don't believe that a DB2 function is flexible to do what you want.  A function is normally called on row data and processes the items in the row.  You/we should be able to process the array, and return any other array with a Stored Procedure.
Tomas Helgi JohannssonCommented:

The most easiest way for this would be to use the ARRAY and ARRAY_AGG functions eg.
SET MY_ARRAY = ARRAY[select stringname from table where condition]; 

Open in new window

in a stored procedure as shown in the above example (the link ;) ).

Another solution if you are passing a delimited string is to create a Java stored procedure and use the simple String.split function to solve this.

Another solution would be to do something recursive like this

     Tomas Helgi
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now