INSERT

Click to skip syntax diagram
Read syntax diagramSkip visual syntax diagram>>-INSERT--(--source-string--,--start--,--length--,--insert-string--)-><
 

Returns a string where length characters have been deleted from source-string beginning at start and where insert-string has been inserted into source-string beginning at start.

source-string
An expression that specifies the source string. The source-string may be any built-in numeric or string expression. It must be compatible with the insert-string. For more information about data type compatibility, see Assignments and comparisons. A numeric argument is cast to a character string before evaluating the function. For more information on converting numeric to a character string, see VARCHAR. The actual length of the string must be greater than zero.
start
An expression that returns a built-in BIGINT, INTEGER, or SMALLINT data type. The integer specifies the starting point 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 built-in BIGINT, INTEGER, or SMALLINT data type. The integer specifies the number of characters that are to be deleted from source-string, starting at the position identified by start. The value of the integer must be in the range of 0 to the length of source-string.
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 or string expression. It must be compatible with the source-string. For more information about data type compatibility, see Assignments and comparisons. A numeric argument is cast to a character string before evaluating the function. For more information on converting numeric to a character string, see VARCHAR. The actual length of the string must be greater than zero.

The data type of the result of the function depends on the data type of the first and fourth 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 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:

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

where:

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 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.

Examples