DB2 10.5 for Linux, UNIX, and Windows

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.


>>-INSERT--(--source-string------------------------------------->

>--,--start--,--length--,--insert-string------------------------>

>--+-----------------------+-----------------------------------><
   '-,--+-CODEUNITS16-+--)-'   
        +-CODEUNITS32-+        
        '-OCTETS------'

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, 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, 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 OCTETS, and insert-string and source-string are binary strings, an error is returned (SQLSTATE 42815). 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
DBCLOB	GRAPHIC, VARGRAPHIC, or 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
BLOB	BLOB	BLOB

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
			CODEUNITS32	A+4*B
		Character with length attribute B	CODEUNITS32	A+4*B
Graphic string with length attribute A	CODEUNITS16	Character with length attribute B	OCTETS	A+B	CODEUNITS16
			CODEUNITS32	A+2*B
		Graphic string with length attribute B	CODEUNITS32	A+2*B

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.

	'&'	'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'