>>-INSERT--(--expression1--,--expression2--,--------------------> >----expression3--,--expression4--)----------------------------><
The schema is SYSFUN.
Returns a string where expression3 bytes have been deleted from expression1 beginning at expression2 and where expression4 has been inserted into expression1 beginning at expression2. If the length of the result string exceeds the maximum for the return type, an error occurs (SQLSTATE 38552).
The first argument is a character string or a binary string type. The second and third arguments must be a numeric value with a data type of SMALLINT or INTEGER. If the first argument is a character string, then the fourth argument must also be a character string. If the first argument is a binary string, then the fourth argument must be a binary string. For a VARCHAR the maximum length is 4 000 bytes and for a CLOB or a binary string the maximum length is 1 048 576 bytes. For the first and fourth arguments, CHAR is converted to VARCHAR and LONG VARCHAR to CLOB(1M), for second and third arguments SMALLINT is converted to INTEGER for processing by the function.
The result is based on the argument types as follows:
The result can be null; if any argument is null, the result is the null value.
Example:
VALUES CHAR(INSERT('DINING', 3, 1, 'VID'), 10)
This example returns the following:
1 ---------- DIVIDING
As mentioned, the output of the INSERT function is VARCHAR(4000). For the above example the function CHAR has been used to limit the output of INSERT to 10 bytes. The starting location of a particular string can be found using LOCATE. Refer to LOCATE for more information.