Page 1 of 1

Using LIKE parameter with % and host variable

PostPosted: Wed Jul 27, 2016 8:52 pm
by chig
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.

Re: Using LIKE parameter with % and host variable

PostPosted: Thu Jul 28, 2016 7:38 pm
by NicC
Add the "%" to the hostvariable in your program then use it properly (:host_var) in your SQL code.

Re: Using LIKE parameter with % and host variable

PostPosted: Thu Jul 28, 2016 7:58 pm
by chig
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 ?

Re: Using LIKE parameter with % and host variable

PostPosted: Thu Jul 28, 2016 8:13 pm
by chig
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.