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)
  1. 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
  1. 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;

Updated on: 04-Dec-2020

5K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements