OVERLAY

The schema is SYSIBM.

The OVERLAY function returns a string where a substring of length, beginning at start has been deleted from source-string, and where insert-string has been inserted into source-string beginning at start. If the value of start plus length is greater than the length of source-string, the substring that is deleted is from start to the end of source-string.

If the length of the result string exceeds the maximum for the return type, an error is returned.

The OVERLAY function is identical to the INSERT function, except that the length argument is optional.

source-string

An expression that specifies the source string. The expression must return a value that is a built-in character string, graphic string, or binary string data type that is not a LOB.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

The actual length of the string must be greater than or equal to 1 byte and less than or equal to 32704 bytes.

insert-string

An expression that specifies the string that is inserted into source-string, starting at the position that is identified by start. insert-string must return a value that is a built-in character string, graphic string, or binary string data type that is not a LOB. source-string and insert-string must have compatible data types.

The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.

start

An expression that returns an integer. The integer specifies the starting point within the source string where the deletion of bytes and the insertion of another string is to begin. The value of the integer must be in the range of 1 to the length of source-string plus one. If OCTETS is specified and the result is graphic data, the value must be an odd value between 1 and twice the length of source-string plus one.

The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34) which is then assigned to an INTEGER value.

length

An expression that specifies the length of the string to replace in source-string starting at start. length must be an expression that returns a value of the built-in INTEGER data type. length is expressed in the string unit specified, and the value must be in the range of 0 to the length of source-string. If OCTETS is specified and the result is graphic data, length must be even and be between 0 and twice the length of source-string. Not specifying length is equivalent to specifying a value of 1, except when OCTETS is specified and the result is graphic data, in which case, not specifying length is equivalent to specifying a value of 2.

The argument can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34) which is then assigned to an INTEGER value.

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the units that are used to express start and length in the result. If source-string is a character string that is defined as bit data, CODEUNITS16 and CODEUNITS32 cannot be specified. If source-string is a graphic string, OCTETS cannot be specified. If source-string is a binary string, CODEUNITS16, CODEUNITS32, and OCTETS cannot be specified.

If a string unit is not explicitly specified, the data type of the result determines the unit that is used. If the result is a graphic string, a string unit is two bytes. For ASCII and EBCDIC data, this corresponds to a double byte character. For Unicode, this corresponds to a UTF-16 code point. Otherwise, a string unit is a byte.

CODEUNITS16

Specifies that start and length are expressed in terms of 16-bit UTF-16 code units.

CODEUNITS32

Specifies that start and length are expressed in terms of 32-bit UTF-32 code units.

OCTETS

Specifies that start and length are expressed in terms of bytes.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String unit specifications. length must be an even number if source-string is graphic data and OCTETS is specified

If source-string and insert-string have different CCSID sets, insert-string (the string to be inserted) is converted to the CCSID of source-string (the source string).

The encoding scheme of the result is the same as source-string. The data type of the result of the function depends on the data type of source-string and insert-string:

• VARCHAR if source-string is a character string. The CCSID of the result depends on the arguments:
  • If either source-string or insert-string is character bit data, the result is bit data.
  • If both source-string and insert-string are SBCS:
    • If both source-string and insert-string are SBCS Unicode data, the CCSID of the result is the CCSID for SBCS Unicode data.
    • If source-string is SBCS Unicode data and insert-string is not SBCS Unicode data, the CCSID of the result is the mixed CCSID for Unicode data.
    • Otherwise, the CCSID of the result is the same as the CCSID of source-string.
  • Otherwise, the CCSID of the result is the mixed CCSID that corresponds to the CCSID of source-string. However, if the input is EBCDIC or ASCII and there is no corresponding system CCSID for mixed, the CCSID of the result is the CCSID of source-string.
• VARGRAPHIC if source-string is a graphic. The CCSID of the result is the same as the CCSID of source-string.
• VARBINARY if source-string and insert-string are both binary strings.

The length attribute of the result depends on the arguments:

• If start and length are constants, the length attribute of the result is:

   L1 - MIN((L1 - V2 + 1), V3) + L4

  where:
  • L1 is the length attribute of source-string
  • V2 is the value of start
  • V3 is the value of length
  • L4 is the length attribute of insert-string
• Otherwise, the length attribute of the result is the length attribute of source-string plus the length attribute of insert-string. In this case, the length attribute of source-string plus the length attribute of insert-string must not exceed 32704 for a VARCHAR result or 16352 for a VARGRAPHIC result.

If CODEUNITS16 or CODEUNITS32 is specified, the insert operation is performed on a Unicode version of the data. If needed, the data is converted to an intermediate form in order to evaluate the function. If an intermediate form is used, the actual length of the result depends on the original data (source-string and insert-string), and the representation of that data in Unicode. See Determining the length attribute of the final result for more information on how to calculate the length attribute of the result string.

A1 - MIN((A1 - V2 + 1), V3) + A4

• A1 is the actual length of source-string
• V2 is the value of start
• V3 is the value of length
• A4 is the actual length of insert-string

If the actual length of the result string exceeds the maximum for the return data type, an error occurs.

The result can be null; if any argument is null, the result is the null value.

   SELECT CHAR(OVERLAY('INSERTING','IS',4,2,OCTETS),10),
          CHAR(OVERLAY('INSERTING','IS',4,0,OCTETS),10),
          CHAR(OVERLAY('INSERTING','',4,2,OCTETS),10)
     FROM SYSIBM.SYSDUMMY1;

   SELECT OVERLAY (:UTF8_VAR, 'C', 1, CODEUNITS16), 
          OVERLAY (:UTF8_VAR, 'C', 1, CODEUNITS32), 
          OVERLAY (:UTF8_VAR, 'C', 1, OCTETS) 
      FROM SYSIBM.SYSDUMMY1

   C N~AB
   CN~AB
   C?N~AB  -- ? is the invalid UTF-8 sequence X'9D849E'

   SELECT OVERLAY (:UTF8_VAR, 'C', 5, CODEUNITS16),
          OVERLAY (:UTF8_VAR, 'C', 5, CODEUNITS32),
          OVERLAY (:UTF8_VAR, 'C', 5, OCTETS)
      FROM SYSIBM.SYSDUMMY1;

   &N~CB
   &N~AC
   &C~AB

    SELECT OVERLAY (:UTF16_VAR, 'C', 1, CODEUNITS16),
           OVERLAY (:UTF16_VAR, 'C', 1, CODEUNITS32)
       FROM SYSIBM.SYSDUMMY1;

   C?N~AB
   CN~AB

   SELECT OVERLAY (:UTF16_VAR, 'C', 5, CODEUNITS16),
          OVERLAY (:UTF16_VAR, 'C', 5, CODEUNITS32),
      FROM SYSIBM.SYSDUMMY1;

   &N~CB
   &N~AC