OVERLAY Function - PostgreSQL to Oracle Migration

In PostgreSQL, OVERLAY function allows you to replace a substring starting at the specified position and having the specified length:

PostgreSQL:

 -- Replace word Edison with Jefferson at 8 position, 6 characters long
 SELECT OVERLAY('Thomas Edison was' PLACING 'Jefferson' FROM 8 FOR 6);
 # Thomas Jefferson was

Implementing OVERLAY Function in Oracle

You can implement OVERLAY function in Oracle using an user-defined function:

Oracle:

  CREATE OR REPLACE FUNCTION overlay(p_string VARCHAR2, p_replace VARCHAR2, 
                                                                p_start NUMBER, p_len NUMBER) 
  RETURN VARCHAR2
  IS
    v_new VARCHAR(2000);
  BEGIN
    -- Copy substring before the start position
    IF p_start > 1 THEN
      v_new := SUBSTR(p_string, 1, p_start - 1);
    END IF;
 
    -- Append the replacement value and the remaining part of the string
    v_new := v_new || p_replace || SUBSTR(p_string, p_start + p_len);
 
    RETURN v_new;
  END;
  /

Now you can use the function in Oracle as follows:

 -- Replace word Edison with Jefferson at 8 position, 6 characters long
 SELECT OVERLAY('Thomas Edison was', 'Jefferson', 8, 6) FROM dual;
 # Thomas Jefferson was

Note that the user-defined function in Oracle uses comma (,) instead of keywords PLACING, FROM and FOR, so you have to modify all SQL statements referencing to this function.