INSERT scalar function

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

Read syntax diagramSkip visual syntax diagramINSERT(source-string ,start,length,insert-string,CODEUNITS16CODEUNITS32OCTETS)

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
source-string insert-string Result
CHAR or VARCHAR CHAR or VARCHAR VARCHAR
GRAPHIC or VARGRAPHIC GRAPHIC or VARGRAPHIC VARGRAPHIC
CLOB CHAR, VARCHAR, or CLOB CLOB
CHAR or VARCHAR CLOB CLOB
DBCLOB GRAPHIC, VARGRAPHIC, or DBCLOB DBCLOB
GRAPHIC or VARGRAPHIC DBCLOB DBCLOB
CHAR or VARCHAR CHAR FOR BIT DATA or VARCHAR FOR BIT DATA VARCHAR FOR BIT DATA
CHAR FOR BIT DATA or VARCHAR FOR BIT DATA CHAR, VARCHAR, CHAR FOR BIT DATA, or VARCHAR FOR BIT DATA VARCHAR FOR BIT DATA
BINARY or VARBINARY BINARY or VARBINARY VARBINARY
BLOB BINARY, VARBINARY, or BLOB BLOB
BINARY or VARBINARY BLOB BLOB
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)
source-string insert-string Result
CHAR or VARCHAR GRAPHIC or VARGRAPHIC VARCHAR
GRAPHIC or VARGRAPHIC CHAR or VARCHAR VARGRAPHIC
CLOB GRAPHIC, VARGRAPHIC, or DBCLOB CLOB
DBCLOB CHAR, VARCHAR, or CLOB DBCLOB

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)
source-string insert-string Result
Data type String units Data type String units Length attribute String units
Character string with length attribute A OCTETS Graphic string with length attribute B CODEUNITS16 A+3*B OCTETS
Character string with length attribute A OCTETS Graphic string with length attribute B CODEUNITS32 A+4*B OCTETS
Character string with length attribute A OCTETS Character with length attribute B CODEUNITS32 A+4*B OCTETS
Graphic string with length attribute A CODEUNITS16 Character with length attribute B OCTETS A+B CODEUNITS16
Graphic string with length attribute A CODEUNITS16 Character with length attribute B CODEUNITS32 A+2*B CODEUNITS16
Graphic string with length attribute A CODEUNITS16 Graphic string with length attribute B CODEUNITS32 A+2*B CODEUNITS16
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 the actual length of the result string exceeds the maximum for the return data type, an error is returned (SQLSTATE 54006).

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

Examples

  • Example 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
  • Example 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
  • Example 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
  • Example 4: Change the string Hegelstraße to Hegelstrasse.
       SELECT INSERT('Hegelstraße',10,1,'ss',CODEUNITS16)
     FROM SYSIBM.SYSDUMMY1
  • Example 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.