How to find and replace text in Oracle?

Problem Statement: You want to find and replace a string in Oracle.

Solution:

1. Function: TRANSLATE
   Syntax: TRANSLATE(expr, from_string, to_string)
   TRANSLATE funtion in oracle allows you to make many single character, one on one substitutions in a single operation.
   However, the to_string and from_string values must not be empty, if an empty string is passed to TRANSLATE Function, Oracle Database interprets the empty string as null and returns null.

/* replace all occurances of b with j in below string */
SELECT 'back and bill ' AS original_string
       ,TRANSLATE('back and bill','b','j') AS replaced_string
  FROM dual;

Output

back and bill   jack and jill

Example

/* to_string with empty value return null */ 
SELECT 'back and bill ' AS original_string
       ,TRANSLATE('back and bill','b','') AS replaced_string
  FROM dual;

Output

back and bill   (null)

Example

/* from_string  with empty value return null */
SELECT 'back and bill ' AS original_string
       ,TRANSLATE('back and bill','','j') AS replaced_string
  FROM dual;

Output

back and bill   (null)

2. Function: REPLACE
   Syntax: REPLACE(expr, search_string, replacement_string )

REPLACE funtion in oracle you to substitute one string for another. REPLACE also allows you to remove any unwanted character strings in the expression.
If the search_string is empty then Oracle returns the original expression with out any changes to it.

Example

/* replace all occurances of b with j in below string */
SELECT 'back and bill ' AS original_string
       ,REPLACE('back and bill','b','j') AS replaced_string
  FROM dual;

Output
back and bill   jack and jill

Example

/* to_string with empty will remove the search_string from the expression */
SELECT 'back and bill ' AS original_string
       ,REPLACE('back and bill','b','') AS replaced_string
  FROM dual;

Output

back and bill   ack and ill

Example

/* search_string with empty value returns original expression */
SELECT 'back and bill ' AS original_string
       ,REPLACE('back and bill','','j') AS replaced_string
  FROM dual;

Output

back and bill   back and bill

3. Function: REGEXP_REPLACE
   Syntax: REGEXP_REPLACE(source_text, pattern, replacement_string, position, occurrence, options)

The REGEXP_REPLACE function is a great way to find and replace strings within a body of text. The function can be used within any Oracle SQL statement or PL/SQL code

pattern: A regular expression or a string of text that will be used to match against.
Replacement string: The string that will replace each occurrence of the string identified.
position : Optional, if specified, Oracle will search from the specified placement with in the source text from this position. By default, the position is 1.
occurrence : The optional occurrence argument is a nonnegative integer that indicates the occurrence of the replace operation.
If a 0 is specified, then all matching occurrences will be replaced.
If a positive not 0 integer is specified, then Oracle will replace the match for that occurrence with the replacement string.

Example

SELECT REGEXP_REPLACE('aaabbbcccdddeeefff','[abcdef]','1') AS replaced_value FROM dual;
SELECT REGEXP_REPLACE('abc','[b]','01') AS replaced_value  FROM dual;
SELECT REGEXP_REPLACE('123-45-6789','[[:digit:]]{3}-[[:digit:]]{2}-[[:digit:]]{4}','xxx-xxx-xxxx')  AS replaced_value  FROM dual;

-- SQL to identify name ROGER and replace it with Rafael.
SELECT full_name AS original_name,
       REGEXP_REPLACE(full_name, 'Rogers','Rafael') AS replaced_name
  FROM customers
WHERE REGEXP_INSTR(UPPER(full_name),'ROGER') > 0  ;

Output

Walter Rogers     Walter Rafael
Kathryn Rogers    Kathryn Rafael

Example

-- PL/SQL program to find and replace a string
-- Declare cursor to to extract rows

DECLARE
  CURSOR cur IS
  SELECT full_name
    FROM customers;

  rec  cur%ROWTYPE;
  new_name customers.full_name%TYPE;

BEGIN
  FOR rec IN cur
     LOOP
        IF REGEXP_INSTR(UPPER(rec.full_name),'ROGER') > 0
      THEN
           new_name := REGEXP_REPLACE(rec.full_name, 'Rogers', 'Rafael');
           DBMS_OUTPUT.PUT_LINE(' Original name ' || rec.full_name || ' is replaced with ' || new_name);
      END IF;
     END LOOP;
END;

Output

Original name Walter Rogers is replaced with Walter Rafael
Original name Kathryn Rogers is replaced with Kathryn Rafael

Data Preparation: Data used for the problem is shown below.

Example

create table customers (
   customer_id     integer generated by default on null as identity,
   email_address   varchar2(255 char) not null,
   full_name       varchar2(255 char) not null)
 ;

  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (1,'tammy.bryant@internalmail','Tammy Bryant');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (2,'roy.white@internalmail','Roy White');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (3,'gary.jenkins@internalmail','Gary Jenkins');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (4,'victor.morris@internalmail','Victor Morris');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (5,'beverly.hughes@internalmail','Beverly Hughes');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (6,'evelyn.torres@internalmail','Evelyn Torres');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (7,'carl.lee@internalmail','Carl Lee');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (8,'douglas.flores@internalmail','Douglas Flores');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (9,'norma.robinson@internalmail','Norma Robinson');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (10,'gregory.sanchez@internalmail','Gregory Sanchez');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (11,'judy.evans@internalmail','Judy Evans');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (12,'jean.patterson@internalmail','Jean Patterson');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (13,'michelle.ramirez@internalmail','Michelle Ramirez');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (14,'elizabeth.martinez@internalmail','Elizabeth Martinez');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (15,'walter.rogers@internalmail','Walter Rogers');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (16,'ralph.foster@internalmail','Ralph Foster');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (17,'tina.simmons@internalmail','Tina Simmons');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (18,'peter.jones@internalmail','Peter Jones');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (19,'kathryn.rogers@internalmail','Kathryn Rogers');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (20,'dennis.lopez@internalmail','Dennis Lopez');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (21,'martha.baker@internalmail','Martha Baker');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (22,'raymond.bailey@internalmail','Raymond Bailey');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (23,'christopher.allen@internalmail','Christopher Allen');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (24,'jonathan.coleman@internalmail','Jonathan Coleman');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (25,'walter.turner@internalmail','Walter Turner');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (26,'anna.murphy@internalmail','Anna Murphy');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (27,'carol.alexander@internalmail','Carol Alexander');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (28,'teresa.brown@internalmail','Teresa Brown');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (29,'beverly.rivera@internalmail','Beverly Rivera');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (30,'lisa.hughes@internalmail','Lisa Hughes');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (31,'deborah.taylor@internalmail','Deborah Taylor');
  insert into customers (CUSTOMER_ID,EMAIL_ADDRESS,FULL_NAME) values (32,'cheryl.young@internalmail','Cheryl Young');
COMMIT;