IBM Mainframe Forum

hi, I am writing a stored proc that is getting a value from input. and I have to search a table using that input.

select *
from table1
where col1 LIKE %inputvar%

I dont' know how to do it.

I used all these things but nothing is working:
a. where col1 LIKE %inputvar%
b. where col1 LIKE '%' || inputvar || '%'

any help is greatly appreciated.

thanks.

Add the "%" to the hostvariable in your program then use it properly (:host_var) in your SQL code.

These users thanked the author NicC for the post:

chig (Thu Jul 28, 2016 7:55 pm)

thanks NicC.

something strange going on.

my database table has these values:
TEST1
TEST2
TEST
TEST ER
TESTER
TE STER

I am sending ST in the input.

Now, with these changes, somehow it is only picking the 3rd record with TEST. it should pick all rows except the last one. but it is not doing that.

these are the changes I did.

WHERE UCASE(col1) LIKE "V_SRCH_NM"

and before the query:

SET V_SRCH_NM = '%'||UCASE(TRIM(SQLI_SRCH_NM))||'%';

Can somebody help what is wrong here ?

ok. now it works.

WHERE UCASE(col1) LIKE TRIM(V_SRCH_NM)

and

SET V_SRCH_NM = '%'||UCASE(TRIM(SQLI_SRCH_NM))||'%';

because the table column and input and variable, all 3 are of 50 chars in length. so have to trim them everywhere.

thanks all.