The INSERT function returns a string where, beginning at start in source-string, length of the specified code units have beendeleted and insert-string has been inserted.

The schema is SYSIBM. The SYSFUN version of the INSERT function continues to be available.

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

source-string

An expression that specifies the source string.
The expression must return a value that is a built-in string, numeric, boolean, or datetime data type.
If the value is not a string data type, it is implicitly cast to VARCHAR before evaluating the function.

start

An expression that returns an integer value.
The integer value specifies the starting point within the source string where the deletion of code units and the insertion of another string is to begin.
The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type.
If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function.
The integer value is the starting point in code units using the specified string units.
The integer value must be between 1 and the actual length of source-string in implicit or explicit string units plus one (SQLSTATE 22001).
If OCTETS is specified and the result is graphic data, the value must be an odd number between 1 and the actual octet length of source-string plus one (SQLSTATE 428GC or 22011).

length

An expression that specifies the number of code units (in the specified string units) that are to be deleted from the source string, starting at the position identified by start.
The expression must return a value that is a built-in numeric, CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC data type.
If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function.
The value must be positive integer or zero (SQLSTATE 22011).
If OCTETS is specified and the result is graphic data, the value must be an even number or zero (SQLSTATE 428GC).

insert-string

An expression that specifies the string to be inserted into source-string, starting at the position identified by start.
The expression must return a value that is a built-in string, numeric, boolean, or datetime data type.
If the value is not a string data type, it is implicitly cast to VARCHAR before evaluating the function.

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the string unit of start and length.

CODEUNITS16 specifies that start and length are expressed in 16-bit UTF-16 code units.
CODEUNITS32 specifies that start and length are expressed in 32-bit UTF-32 code units.
OCTETS specifies that start and length are expressed in bytes.

If the string unit is specified as CODEUNITS16 or CODEUNITS32, and the result is a binary string or bit data, an error is returned (SQLSTATE 428GC).
If the string unit is specified as CODEUNITS16 or OCTETS, and the string units of source-string is CODEUNITS32, an error is returned (SQLSTATE 428GC).
If the string unit is specified as OCTETS, the operation is performed in the code page of the source-string.
If a string unit is not explicitly specified, the string unit of the source-string determines the unit that is used.
For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String units in built-in functions in Character strings.

The data type of the result depends on the data types of source-string and insert-string, as shown in the following tables of supported type combinations.
The string unit of the result is the string unit of source-string.
If either source-string or insert-string is defined as FOR BIT DATA the other argument cannot be defined with string units of CODEUNITS32.
The second table applies only to Unicode databases.

Table 1. Data type of the result as a function of the data types of source-string and insert-string

Note:

If source-string or insert-string is a binary data type and the other is a FOR BIT DATA string, the argument that is not a binary data type is handled as if it was cast to the corresponding binary data type.

Table 2. Data type of the result as a function of the data types of source-string and insert-string (Unicode databases only)

A source-string can have a length of 0; in this case, start must be 1 (as implied by the bounds for start described previously), and the result of the function is a copy of the insert-string.

An insert-string can also have a length of 0. This has the effect of deleting the code units identified by start and length from the source-string.

The length attribute of the result is the length attribute of source-string plus the length attribute of insert-string when the string units of the source-string and insert-string are the same or the result string units is CODEUNITS32. Special cases are listed in the following table.

Table 3. Data type of the result as a function of the data types of source-string and insert-string (special cases)

The actual length of the result depends on the actual length of source-string, the actual length of the of the deleted string, the actual length of the insert-string, and string units used for the start and length arguments.
For example, if the string arguments are character strings in OCTETS and the OCTETS is used as the fourth argument, 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 any argument can be null, the result can be null; if any argument is null, the result is the null value.

Examples

1. Create the strings INSISTING, INSISERTING, and INSTING from the string INSERTING by inserting text into the middle of the existing text.

      SELECT INSERT('INSERTING',4,2,'IS'),
             INSERT('INSERTING',4,0,'IS'),
             INSERT('INSERTING',4,2,'')
      FROM   SYSIBM.SYSDUMMY1

2. Create the strings XXINSERTING, XXNSERTING, XXSERTING, and XXERTING from the string INSERTING by inserting text before the existing text, using 1 as the starting point.

      SELECT INSERT('INSERTING',1,0,'XX'),
             INSERT('INSERTING',1,1,'XX'),
             INSERT('INSERTING',1,2,'XX'),
             INSERT('INSERTING',1,3,'XX')
      FROM SYSIBM.SYSDUMMY1

3. Create the string ABCABCXX from the string ABCABC by inserting text after the existing text.
   Because the source string is 6 characters long, set the starting position to 7 (one plus the length of the source string).

      SELECT INSERT('ABCABC',7,0,'XX')
      FROM   SYSIBM.SYSDUMMY1

4. Change the string Hegelstraße to Hegelstrasse.

      SELECT INSERT('Hegelstraße',10,1,'ss',CODEUNITS16)
      FROM   SYSIBM.SYSDUMMY1

5. The following example works with the Unicode string &N~AB, where & is the musical symbol G clef character, and ~ is the combining tilde character.
   This string is shown in different Unicode encoding forms in the following example:
   
   

   	&	N	~	A	B
   UTF-8	X'F09D849E'	X'4E'	X'CC83'	X'41'	X'42'
   UTF-16BE	X'D834DD1E'	X'004E'	X'0303'	X'0041'	X'0042'

   Assume that the variables UTF8_VAR and UTF16_VAR contain the UTF-8 and the UTF-16BE representations of the string, respectively.
   Use the INSERT function to insert a C into the Unicode string &N~AB.

      SELECT INSERT(UTF8_VAR, 1, 4, 'C', CODEUNITS16),
             INSERT(UTF8_VAR, 1, 4, 'C', CODEUNITS32),
             INSERT(UTF8_VAR, 1, 4, 'C', OCTETS)
      FROM   SYSIBM.SYSDUMMY1

   returns the values CAB, CB, and CN~AB, respectively.

      SELECT INSERT(UTF8_VAR, 5, 1, 'C', CODEUNITS16),
             INSERT(UTF8_VAR, 5, 1, 'C', CODEUNITS32),
             INSERT(UTF8_VAR, 5, 1, 'C', OCTETS)
      FROM   SYSIBM.SYSDUMMY1

   returns the values &N~CB, &N~AC, and &C~AB, respectively.

      SELECT INSERT(UTF16_VAR, 1, 4, 'C', CODEUNITS16),
             INSERT(UTF16_VAR, 1, 4, 'C', CODEUNITS32),
             INSERT(UTF16_VAR, 1, 4, 'C', OCTETS)
      FROM   SYSIBM.SYSDUMMY1

   returns the values CAB, CB, and CN~AB, respectively.

      SELECT INSERT(UTF16_VAR, 5, 2, 'C', CODEUNITS16),
             INSERT(UTF16_VAR, 5, 1, 'C', CODEUNITS32),
             INSERT(UTF16_VAR, 5, 4, 'C', OCTETS)
      FROM   SYSIBM.SYSDUMMY1

   returns the values &N~C, &N~AC, and &CAB, respectively.