Performing multiple Replace operations on an Oracle database column

Question

I currently have a bunch of strings inside an Oracle database column that I need to change, right now I am running multiple update statements to accomplish this.

UPDATE TABLE1 SET COLUMN1 = REPLACE 
(COLUMN1, 'ABC', 'SR1')
UPDATE TABLE1 SET COLUMN1 = REPLACE  
(COLUMN1, '123', 'SR2')
UPDATE TABLE1 SET COLUMN1 = REPLACE  
(COLUMN1, 'XYZ', 'SR3')
UPDATE TABLE1 SET COLUMN1 = REPLACE    
(COLUMN1, '789', 'SR4')

Is there anyway for me to get this done in one go around for example

UPDATE TABLE1 
SET
COLUMN1 = REPLACE(COLUMN1, 'XYZ', 'SR3'), 
COLUMN1 = REPLACE(COLUMN1, '789', 'SR4')

Gordon Linoff · Accepted Answer · 2018-04-13 15:53:46Z

8

You can nest the replace() calls:

UPDATE TABLE1
    SET COLUMN1 = REPLACE(REPLACE(REPLACE(REPLACE(COLUMN1, 'ABC', 'SR1'), '123', 'SR2'), 'XYZ', 'SR3'), '789', 'SR4');

Oracle also offers regexp_replace(). You might find this handy for some of your data transformations.

answered Apr 13, 2018 at 15:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Lukasz Szozda Over a year ago

You were faster :), just curious what is max nested func limit (255 or 32)?

jdmneon Over a year ago

how would I use regexp_replace to achieve the same thing? I'm worried I could run up against the maximum level nested functions

Gordon Linoff Over a year ago

@lad2025 . . . I happen to know that in SQL Server it is about 32. I don't know what the limit is in Oracle.

Kaushik Nayak · Accepted Answer · 2018-04-13 16:32:38Z

You may create an UDF using an associative array.

CREATE OR REPLACE  FUNCTION my_replace (
    inp VARCHAR2
) RETURN VARCHAR2 IS
    v_out   VARCHAR2(1000) := inp;
    TYPE v_astype IS
        TABLE OF VARCHAR2(40) INDEX BY VARCHAR(40);
    v_pat      v_astype;
    v_idx      VARCHAR2(40);
BEGIN
   v_pat('ABC') := ('SR1');
   v_pat('123') := ('SR2');
   v_pat('XYZ') := ('SR3');
   v_pat('789') := ('SR4');
    v_idx := v_pat.first;
    WHILE v_idx IS NOT NULL LOOP
        v_out := replace(v_out,v_idx,v_pat(v_idx) );
        v_idx := v_pat.next(v_idx);
    END LOOP;
    RETURN v_out;
END; 
/

At least now, your update statement will look clean :

UPDATE TABLE1
    SET COLUMN1 = my_replace(column1) ;

Also, for Oracle 12c and above you may use inline function ( it works if you just want to use function in a select ).

https://oracle-base.com/articles/12c/with-clause-enhancements-12cr1

Thorsten Kettner · Accepted Answer · 2018-04-13 16:24:48Z

0

As this is Oracle you can write a PL/SQL function to do the conversion:

create or replace function multi_replace(vi_string varchar2) return varchar2 is
  vo_string varchar2(4000);
begin
  vo_string := vi_string;
  vo_string := replace(vo_string, 'XYZ', 'SR3');
  vo_string := replace(vo_string, '789', 'SR4');
  ...
  return vo_string;
end;

Then call it in a query:

update mytable set column1 = multi_replace(column1);

answered Apr 13, 2018 at 16:24

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Collectives™ on Stack Overflow

Performing multiple Replace operations on an Oracle database column

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related