LIKE keyword.

[tinkuboy]

Hi,

I have a stored procedure which doesnt work the problem comes with select statement and that too with the LIKE keyword. Any suggestion how to use LIKE keyword in a select statement in a cursor appreciated.

Thanks.

create procedure SPQTBYNAME(
IN PRMLASTNAME CHARACTER (25)
)
LANGUAGE SQL
RESULT SETS 1

BEGIN

DECLARE LASTNAME CHARACTER (26) ;

DECLARE QUOTECUR CURSOR WITH RETURN FOR SELECT QUOTENUMBER,INSFIRSTNAME AS NAME,EFFECTIVEDATE,POLICYPREMIUM FROM HOME01 WHERE INSLASTNAME LIKE LASTNAME ;

SET LASTNAME = CONCAT(PRMLASTNAME,'%');

OPEN QUOTECUR ;

END

 

[blom0344]

You are using the LIKE operator in a way it isn't meant to work. With LIKE you can compare part of a string with an expression like:

field LIKE 'abc%' which means strings like 'abcde' will are validated positively and 'xabcy' not.

Perhaps using the SOUNDEX function will be a better bet in this case.However the outcome is not 100% predictable...


T. Blom
Information analyst
tbl@shimano-eu.com

 

[sathyarams]

Or will a function like POSSTR help ?

Cheers

sathyaram

More DB2 questions answered at

http://www.dbforums.com/f8

 

[tinkuboy]

Hi ,

Thanks for the reply.

I used LIKE in my select statement for getting all the names starting with a specified letter eg the user enters 's' then the select statemnt retrieves all the names starting with 's'.

I got it working i had to trim some of the variables.

Thanks.