asked on

How to Write & call a user defined function in UDB stored procedure?

Hi Experts,
I need to write a function which should take a character parameter & return a character paramenter, this function will be in UDB stored procedure & also have call from same SP. Can anybody help how to write a function UDB SP & make call to it in same SP. Appreciate any sample code for same.

Regards
Ams

momi_sabag

what should the function do?

Amit_Pekamwar

ASKER

It should -
accept a character string
Remove non numeric values from that string &
Return the string with only numeric values in it.

momi_sabag

udb already has a built in function for that, sort of, you can do it without creating your own function like this:

select
replace(translate(your_string, '', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'), ' ', '')

read about translate:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0000862.htm

and replace:
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0000843.htm

Amit_Pekamwar

ASKER

Thanks Momi,
we tried translate & it is not working as per our requirement. Hence we are using our own logic to convert same. That logic we need to execute multiple times in our SP. So thinking if we can incorporate a function in SP & call that function multiple times.

ASKER CERTIFIED SOLUTION

momi_sabag

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amit_Pekamwar

ASKER

Thanks Momi. Example provided is working fine & I have created similar function for my functionality. Thanks a lot again.

How to Write &amp; call a user defined function in UDB stored procedure?

How to Write & call a user defined function in UDB stored procedure?