Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


DB2 - How to create a function that takes dynamic input

Posted on 2017-08-10
Medium Priority
Last Modified: 2017-08-25
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 ?
Question by:FranklinRaj22
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 46

Expert Comment

by:Kent Olsen
ID: 42250164
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.


Author Comment

ID: 42250183

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

Expert Comment

by:Kent Olsen
ID: 42250219
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.
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 42253943

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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Join & Write a Comment

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
What we learned in Webroot's webinar on multi-vector protection.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA:…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question