SQL Reference

INSERT

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

• VARCHAR(4000) if both the first and fourth arguments are VARCHAR (not exceeding 4 000 bytes) or CHAR
• CLOB(1M) if either the first or fourth argument is CLOB or LONG VARCHAR
• BLOB(1M) if both first and fourth arguments are BLOB.

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

Example:

• Delete one character from the word 'DINING' and insert 'VID', both beginning at the third character.

  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.