Replace multiple strings in Oracle [duplicate]

Question

I have to remove a specific set of words from a string.
E.g. following words needs to removed (ooh,aah and etc) from an employee_description.
Below query I have tried:

SELECT REPLACE(REPLACE(emp_desc, 'aah', ''), 'ooh', '')
from TEST_Table;

It's working fine.

But is there any other Best Way to do this.

And by "best way" you meant what exactly? Is this query slow? Or there are other reasons that make you search for a "best way". — Nick Krasnov
– Nick Krasnov, Commented Jul 6, 2015 at 8:15
@NicholasKrasnov: I guess OP doesn't want to call replace on each pattern.. — souslov
– souslov, Commented Jul 6, 2015 at 8:16
@PrakashBaskaran, define "best". What you are doing is fine. What is the problem, is the query slower? Also, you might want to trim extra spaces in the output. — Lalit Kumar B
– Lalit Kumar B, Commented Jul 6, 2015 at 8:18

souslov · Accepted Answer · 2015-07-06 08:23:36Z

3

You can try with regexp_replace:

select regexp_replace(emp_desc, 'aah|ooh', '') from TEST_Table;

SQLFiddle

edited Jul 6, 2015 at 8:23

answered Jul 6, 2015 at 8:15

souslov

44.7k11 gold badges92 silver badges113 bronze badges

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

1 Comment

Lalit Kumar B Over a year ago

Remember, REGEXP_REPLACE is not faster than REPLACE. It would consume more CPU.

Lalit Kumar B · Accepted Answer · 2015-07-06 08:27:14Z

1

But is there any other Best Way to do this.

Define "best". I think your query is fine, unless you are having any performance issues. By the way, REGEXP_REPLACE would be a costlier operation, and would be slower than traditional REPLACE. Regular expressions are CPU intensive operations, though with newer releases the difference in performance is narrowing down, however, they are still slower than traditional functions.

By the way, you might want to TRIM the extra space after replacing the string.

SQL> column str format a5
SQL> WITH DATA(emp_desc) AS(
  2  SELECT 'scott aah' FROM dual UNION ALL
  3  SELECT 'allen ooh' FROM dual
  4  )
  5  SELECT TRIM(REPLACE(REPLACE(emp_desc,'aah'),'ooh')) str FROM DATA;

STR
-----
scott
allen

SQL>

edited Jul 6, 2015 at 8:27

answered Jul 6, 2015 at 8:24

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

1 Comment

Lalit Kumar B Over a year ago

@PrakashBaskaran You're welcome. Please mark it as answered, would help others. Also, please reply to the comments above on your question.

Collectives™ on Stack Overflow

Replace multiple strings in Oracle [duplicate]

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related