OVERLAY()

Tip

Find a more recent version at Firebird 5.0 Language Reference: OVERLAY()

Available in: DSQL, PSQL

Added in: 2.1

Description: Replaces part of a string with another string. By default, the number of characters removed from the host string equals the length of the replacement string. With the optional fourth argument, the user can specify a different number of characters to be removed.

Result type: VARCHAR or BLOB

Syntax:

OVERLAY (string PLACING replacement FROM pos [FOR length])
This function supports BLOBs of any length. Due to a bug in versions 2.1–2.1.4, BLOBs containing multi-byte characters – and sometimes even single-byte non-ASCII characters – will cause a “Cannot transliterate character between character sets” error. This has been fixed for Firebird 2.1.5.

If string or replacement is a BLOB, the result is a BLOB. Otherwise, the result is a VARCHAR(n) with n the sum of the lengths of string and replacement.

As usual in SQL string functions, pos is 1-based.

If pos is beyond the end of string, replacement is placed directly after string.

If the number of characters from pos to the end of string is smaller than the length of replacement (or than the length argument, if present), string is truncated at pos and replacement placed after it.

The effect of a “FOR 0” clause is that replacement is simply inserted into string.

If any argument is NULL, the result is NULL.

If pos or length is not a whole number, bankers' rounding (round-to-even) is applied, i.e. 0.5 becomes 0, 1.5 becomes 2, 2.5 becomes 2, 3.5 becomes 4, etc.

Examples:

overlay ('Goodbye' placing 'Hello' from 2)    -- returns 'GHelloe'
overlay ('Goodbye' placing 'Hello' from 5)    -- returns 'GoodHello'
overlay ('Goodbye' placing 'Hello' from 8)    -- returns 'GoodbyeHello'
overlay ('Goodbye' placing 'Hello' from 20)   -- returns 'GoodbyeHello'

overlay ('Goodbye' placing 'Hello' from 2 for 0)   -- r. 'GHellooodbye'
overlay ('Goodbye' placing 'Hello' from 2 for 3)   -- r. 'GHellobye'
overlay ('Goodbye' placing 'Hello' from 2 for 6)   -- r. 'GHello'
overlay ('Goodbye' placing 'Hello' from 2 for 9)   -- r. 'GHello'

overlay ('Goodbye' placing '' from 4)         -- returns 'Goodbye'
overlay ('Goodbye' placing '' from 4 for 3)   -- returns 'Gooe'
overlay ('Goodbye' placing '' from 4 for 20)  -- returns 'Goo'

overlay ('' placing 'Hello' from 4)           -- returns 'Hello'
overlay ('' placing 'Hello' from 4 for 0)     -- returns 'Hello'
overlay ('' placing 'Hello' from 4 for 20)    -- returns 'Hello'

Warning

When used on a BLOB, this function may need to load the entire object into memory. This may affect performance if huge BLOBs are involved.