I want to use the REPLACE function in the Where clause to replace a character that I put into a input box. For example if I input
R123;R321 I want it to change my input to
Example of my query:
Select order_no From order Where order_no in ('&Order_No')
I input the data with this box:
Any help would be appreciated, or if there is some other way to do it without the REPLACE function.
That won’t work like this, I’m afraid. You’ll have to split input value into rows, e.g.
SQL> select deptno, ename 2 from emp 3 where deptno in (select regexp_substr('&&deptnos', '[^;]+', 1, level) 4 from dual 5 connect by level <= regexp_count('&&deptnos', ';') + 1 6 ) 7 order by deptno, ename; Enter value for deptnos: 10;20 DEPTNO ENAME ---------- ---------- 10 CLARK 10 KING 10 MILLER 20 ADAMS 20 FORD 20 JONES 20 SCOTT 20 SMITH 8 rows selected. SQL>