How to replace string in Oracle?

Question

From front end User will enter (multiple) employee number as (123,456,789) to search in the application. The way User enters the employee numbers is pre-defined like above.

This value has to be searched in a Oracle database table in a PL/SQL package.

I have coded like below to format the entered value so that it can be searched in the SQL WHERE condition.

lv_where := REPLACE( REPLACE(  REPLACE( '(123,456,789)', ',', ''',''' ), '(', '(''') , ')', ''')')

Output is : ('123','456','789')

Is there a better way?

hotfix · Accepted Answer · 2019-12-06 12:51:56Z

3

you can use regexp_replace

regexp_replace('(123,456,789)','([[:digit:]]+)','''\1''')

Result:
('123','456','789')

answered Dec 6, 2019 at 12:51

hotfix

3,39624 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Littlefoot Over a year ago

Right, but - that won't help in what the OP said - use it in WHERE clause :)

hotfix Over a year ago

but he not said, how he use it in the where clause. maybe he use dynamic sql :)

Littlefoot · Accepted Answer · 2019-12-06 12:49:39Z

Unless it is dynamic SQL, such a lv_where won't work. I'd suggest you to split entered values into rows and use them in your query.

In the following example, par_emp represents value entered by user. Basically, what you need begins at line #3.

SQL> with test (par_emp) as
  2    (select '(10,20,30)' from dual)
  3  select deptno, ename
  4  from emp
  5  where deptno in (select regexp_substr(replace(replace(par_emp, '(', ''), ')', ''), '[^,]+', 1, level)
  6                   from test
  7                   connect by level <= regexp_count(par_emp, ',') + 1
  8                  )
  9  order by deptno;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 JONES
        20 FORD
        20 ADAMS
        20 SMITH
        20 SCOTT
        30 WARD
        30 TURNER
        30 ALLEN
        30 JAMES
        30 BLAKE
        30 MARTIN

14 rows selected.

SQL>

Collectives™ on Stack Overflow

How to replace string in Oracle?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related