REPLACE : CHAR VARCHAR2 Functions « PL SQL Data Types « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Data Types
3. CHAR VARCHAR2 Functions

The REPLACE functions transforms text by using the specified pattern:

v_tx:= replace(string,search[,replacement]);

SQL> declare
  2      v1_tx VARCHAR2(20):='To be or not to be';
  3      v2_tx VARCHAR2(20);
  4  begin
  5      DBMS_OUTPUT.put_line('Before: ' || v1_tx);
  6      v2_tx:= replace (v1_tx,'be','eat');
  7      DBMS_OUTPUT.put_line('After: ' || v2_tx);
  8    end;
  9  /
Before: To be or not to be
After: To eat or not to eat

PL/SQL procedure successfully completed.

If you don't specify the third parameter, Oracle just removes all occurrences of the search string.

This is very useful if you want to remove all the spaces from the text.