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.

More Information

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - March 2013.

You could leave a comment if you were logged in.