Using LIKE parameter with % and host variable



IBM's flagship relational database management system

Using LIKE parameter with % and host variable

Postby chig » Wed Jul 27, 2016 8:52 pm

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.
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Using LIKE parameter with % and host variable

Postby NicC » Thu Jul 28, 2016 7:38 pm

Add the "%" to the hostvariable in your program then use it properly (:host_var) in your SQL code.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic

These users thanked the author NicC for the post:
chig (Thu Jul 28, 2016 7:55 pm)
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Using LIKE parameter with % and host variable

Postby chig » Thu Jul 28, 2016 7:58 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 ?
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Using LIKE parameter with % and host variable

Postby chig » Thu Jul 28, 2016 8:13 pm

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.
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post