Passing a variable into 'like' condition in a query

[coolmandba]

Hi all,

Does anyone know how to pass a variable into 'like' condition in a query?

This is what I'm trying to achieve. Say if I have a procedure:

****************************
create or replace procedure test
(searchstr varchar2)

IS
y varchar2(20)
Begin
insert into y
select name from employee where name like '%searchstr%';
dbms_output.put_line(y);
End;
***************************

Here searchstr is a input parameter from user. If searchstr='dav', then I want 'dav' to be plugged-in to the like condition.
I know the above isn't right, so what's the correct syntax?
Thanks

[m_d_marian]

try this

select name into y from employee where name like '%' || searchstr || '%' ;

[coolmandba]

Thanks I'll try that.

[hptse]

Simply declare a variable to hold the input string and execute the sql.

example:

create table exptest as select * from all_users;
delete exptest ;
declare
b varchar2(10);
begin
b := '%A%';
insert into exptest select * from all_users where username like b;
end;
/