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
VALUES OVERLAY('THE MEETING ON x IS IN ONLY 5 HOURS!', CURRENT DATE,16);
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)
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)
Result: I ALWAYS WORK UNTIL 15.38.45
Was this topic helpful?
13 January 2020