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 :
CREATE FUNCTIONSPLIT_STRING (varchar string)
  RETURNS TABLE or row  ( string1 VARCHAR(100),string2 VARCHAR(100))
 
   RETURN values (tempstring,tempstring1);--
END;

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 ?
FranklinRaj22Asked:
Who is Participating?
 
Tomas Helgi JohannssonConnect With a Mentor Commented:
Hi!

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

Regards,
     Tomas Helgi
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Franklin,

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

CREATE FUNCTIONSPLIT_STRING (varchar string)
  RETURNS TABLE or row  ( string1 VARCHAR(100),string2 VARCHAR(100))
 
  RETURN 
    SELECT 
      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;
  END;
 

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.


Kent
0
 
FranklinRaj22Author Commented:
Kent

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 .
0
 
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.
0
All Courses

From novice to tech pro — start learning today.