Re: How to use variable in the LIKE function along with % operators

In message <1164613610.617743.218690_at_h54g2000cwb.googlegroups.com>, sangu_rao_at_yahoo.co.in writes
>Hi,
>Is there any way i can use the variable in the Like function. That
>means i have query like
>
>SELECT * FROM Device WHERE DeviceName LIKE %v_MediaName%;
>
>Here "v_MediaName" is the userdefined variable which contains string. I
>want to retrieve all the records from the "DEVICE" table whose
>DeviceName LIKE %v_MediaName%;
>
>If i put it in a single quotes '%v_MediaName%' then the v_MediaName
>will be treaded as a string instead of a variable. I am using this
>query in a Procedure.
>
>please help me out to resolve the issue.
>
>thanks
>Rao
>

You need to concatenate the string variable with the string '%'

e.g.

select * from device where DeviceName like '%'||v_medianame||'%';

However you will get better performance and scalability if you use a bind variable as the expression.

v_medianame := '%'||v_medianame||'%';

select * from device where DeviceName like v_medianame;

-- 
Jim Smith
Ponder Stibbons Limited <http://oracleandting.blogspot.com/>
RSS <http://oracleandting.blogspot.com/atom.xml>