OVERLAY() built-in function

OVERLAY is another new function we’re introducing. OVERLAY is a beefed-up version of INSERT. It has support for parameter markers and date/time/timestamp data types for the source-string and insert-string operands. The ‘length’ parameter is now optional with a default value of 1.

As you can see in the following examples, OVERLAY simplifies string manipulation and removes the need for numerous calls to SUBSTR or CONCAT, depending on what your needs.
 

For complete details, refer to the SQL Reference detail found here: OVERLAY scalar function

Examples:

VALUES OVERLAY('THE MEETING ON x IS IN ONLY 5 HOURS!', CURRENT DATE,16);
                1234567890123456

Result: THE MEETING ON 2015-10-04 IS IN ONLY 5 HOURS!    

Because you can specify how much of the original string should be deleted, you can add placeholders to the original string for readability.

VALUES OVERLAY('ON xx IT WILL BE POPULAR TO WEAR SOCKS WITH SANDALS' ,
                         CURRENT DATE + 10 YEARS, 4, 2)
                1234

Result: ON 2025-10-04 IT WILL BE POPULAR TO WEAR SOCKS WITH SANDALS    

As with INSERT, OVERLAY can delete ‘length’ worth of text, up to the full length of the source string, and can be called using keywords instead of the standard parameter style.

VALUES OVERLAY('I ALWAYS WORK UNTIL THE JOB IS DONE' PLACING CURRENT TIME FROM 21 FOR 15)
                123456789012345678901

Result: I ALWAYS WORK UNTIL 15.38.45