SQL Oracle – Replace function in Where Clause

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 R123','R321.

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.

Answer

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>