8.3.10. OVERLAY()

Available in

DSQL, PSQL

Syntax

  1. OVERLAY (string PLACING replacement FROM pos [FOR length])
Table 132. OVERLAY Function Parameters
ParameterDescription

string

The string into which the replacement takes place

replacement

Replacement string

pos

The position from which replacement takes place (starting position)

length

The number of characters that are to be overwritten

Result type

VARCHAR or BLOB

Description

OVERLAY() overwrites part of a string with another string. By default, the number of characters removed from (overwritten in) the host string equals the length of the replacement string. With the optional fourth argument, a different number of characters can be specified for removal.

  • This function supports BLOBs of any length.

  • 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

  1. overlay ('Goodbye' placing 'Hello' from 2)   -- returns 'GHelloe'
  2. overlay ('Goodbye' placing 'Hello' from 5)   -- returns 'GoodHello'
  3. overlay ('Goodbye' placing 'Hello' from 8)   -- returns 'GoodbyeHello'
  4. overlay ('Goodbye' placing 'Hello' from 20)  -- returns 'GoodbyeHello'
  6. overlay ('Goodbye' placing 'Hello' from 2 for 0) -- r. 'GHellooodbye'
  7. overlay ('Goodbye' placing 'Hello' from 2 for 3) -- r. 'GHellobye'
  8. overlay ('Goodbye' placing 'Hello' from 2 for 6) -- r. 'GHello'
  9. overlay ('Goodbye' placing 'Hello' from 2 for 9) -- r. 'GHello'
  11. overlay ('Goodbye' placing '' from 4)        -- returns 'Goodbye'
  12. overlay ('Goodbye' placing '' from 4 for 3)  -- returns 'Gooe'
  13. overlay ('Goodbye' placing '' from 4 for 20) -- returns 'Goo'
  15. overlay ('' placing 'Hello' from 4)          -- returns 'Hello'
  16. overlay ('' placing 'Hello' from 4 for 0)    -- returns 'Hello'
  17. overlay ('' placing 'Hello' from 4 for 20)   -- returns 'Hello'

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.

See also

REPLACE()