OVERLAY

source-string

An expression that specifies the source string. The source-string may be any built-in numeric, string, or datetime expression. It must be compatible with the insert-string. For more information about data type compatibility, see Assignments and comparisons. A numeric or datetime argument is cast to VARCHAR with a CCSID that is the default SBCS CCSID at the current server before evaluating the function. For more information about converting numeric or datetime to a varying character string, see VARCHAR. The actual length of the string must be greater than zero.

The OVERLAY function is identical to the INSERT function except that the arguments are in a different order and length is optional.

insert-string

An expression that specifies the string to be inserted into source-string, starting at the position identified by start. The insert-string may be any built-in numeric, string, or datetime expression. It must be compatible with the source-string. For more information about data type compatibility, see Assignments and comparisons. A numeric or datetime argument is cast to VARCHAR with a CCSID that is the default SBCS CCSID at the current server before evaluating the function. For more information about converting numeric or datetime to a varying character string, see VARCHAR.

start

An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. If value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The integer specifies the starting character within source-string where the deletion of characters 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.

length

An expression that returns a value of any built-in numeric, character-string, or graphic-string data type. If value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function. The integer specifies the number of characters that are to be deleted from source-string, starting at the character position identified by start. The value of the integer must be in the range of 0 to the length of source-string.

The data type of the result of the function depends on the data type of the first and second arguments. The result data type is the same as if the two arguments were concatenated except that the result is always a varying-length string. For more information see Conversion rules for operations that combine 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 depends on the encoding schema of source-string:
  - If the source-string is UTF-8, the value MIN(L1+1,start*3)
  - If the source-string is mixed data, the value MIN(L1+1,(start-1)*2.5+4)
  - Otherwise, 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.

If the length attribute of the result exceeds the maximum for the result data type, an error is returned.

The actual length of the result is:

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

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

The CCSID of the result is determined by the CCSID of source-string and insert-string. The resulting CCSID is the same as if the two arguments were concatenated. For more information, see Conversion rules for operations that combine strings.