LIKE in a UDF

gamo · #1 (**permalink**) 11-12-09, 18:46

Hi!

I am a bit stuck (DB2 9.5)

here is my UDF

Code:

CREATE FUNCTION myfunc( a varchar(1), b varchar(1) ) returns varchar( 1 )
DETERMINISTIC NO EXTERNAL ACTION 
BEGIN ATOMIC
DECLARE tmpstr varchar(2);
DECLARE result varchar(1);
SET tmpstr = a||b;
SET result  = select col from table where refcol like '&#37;'||tmpstr||'%';
RETURN result;
END

and here is the error which I am getting:
SQL0132N A LIKE predicate or POSSTR scalar function is not valid because the first operand is not a string expression or the second operand is not a string

any ideas how I could properly cast the inputs so that I can use LIKE in this way??

stolze · #2 (**permalink**) 11-12-09, 23:18

The patter for a LIKE predicate must be constant. In your case, it depends on the input parameters and is not constant.

gamo · #3 (**permalink**) 11-12-09, 23:21

Yes, I understand that - what I was wondering is if there is some way that I can work around this fact.

stolze · #4 (**permalink**) 11-13-09, 00:14

If you are just looking for substrings, you can use LOCATE(). If you want to allow more general patterns, regular expression matching may be something for you: Bringing the Power of Regular Expression Matching to SQL

rahul_s80 · #5 (**permalink**) 11-13-09, 00:14

Usage of LIKE with content from another cell

gamo · #6 (**permalink**) 11-13-09, 00:17

ah - thankyou - I didn't see that one!

helps when I search for the right thing

Internet Services

Web Development

Digital Marketing

Consumer Tech