 Like function with Parameters [message #618397] |
Fri, 11 July 2014 08:54  |
 |
itmasterw
Messages: 13
Registered: July 2014
Location: NY
|
Junior Member |
|
|
Hi,
I am trying to use the Like function with an input Parameter.
Somethign like this:
Select *
From T1
Where ID Like :Input_Id
I tryed
Where ID Like %:Input_Id%
But that did not work
Any ideas would be appreciated
Thak you
[Updated on: Fri, 11 July 2014 08:55] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Like function with Parameters [message #618404 is a reply to message #618397] |
Fri, 11 July 2014 09:06  |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
something like this?
jw_1>
jw_1> var v1 varchar2(10)
jw_1> exec :v1:='LL'
PL/SQL procedure successfully completed.
jw_1> select * from scott.emp where ename like '%'||:v1||'%';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
jw_1> |
|
|
|
|
|
| Re: Like function with Parameters [message #618410 is a reply to message #618404] |
Fri, 11 July 2014 09:31 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
John Watson wrote on Fri, 11 July 2014 19:36jw_1> select * from scott.emp where ename like '%'||:v1||'%';
@itmasterw, in John's demo, you could also have it directly as "like :v1", but the "%" needs to be included in the variable.
SQL> var v1 varchar2(10);
SQL> exec :v1:='%LL%';
PL/SQL procedure successfully completed.
SQL> SELECT * from emp where ename like :v1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 02/20/1981 00:00:00 1600 300 30
7934 MILLER CLERK 7782 01/23/1982 00:00:00 1300 10
|
|
|
|
|
|
|
|
| Re: Like function with Parameters [message #618440 is a reply to message #618419] |
Fri, 11 July 2014 13:06 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Fri, 11 July 2014 11:59But remember, John's demo is generic and can be used in any scenario.
Not exactly. Generic solution would be:
like '%' || regexp_replace(:v1,'(\\|%|_)','\\\1') || '%' escape '\'
John's solution will work if :v1 doesn't contain wildcard characters (% or _).
SY.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
