INSERT
The INSERT function returns a string where, beginning at start in source-string, length characters have been deleted and insert-string has been inserted.
>>-INSERT--(--source-string--,--start--,--length--,--insert-string--+--------------------+--)->< '-,--+-CODEUNITS16-+-' +-CODEUNITS32-+ '-OCTETS------'
The schema is SYSIBM.
The INSERT function returns a string where length characters have been deleted from source-string, beginning at start, and where insert-string has been inserted into source-string, beginning at start.
- source-string
- An expression that specifies the source string. The
expression must return a value that is a built-in character string,
graphic string, or binary string data type that is not a LOB. The
actual length of the string must be greater than or equal to 1 and
less than or equal to 32704 bytes.
The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.
- start
- An expression that returns an integer. The integer specifies the
starting point within the source string where the deletion of bytes
and the insertion of another string is to begin. The value of the
integer must be in the range of 1 to the length of source-string plus
one. If OCTETS is specified and the result is graphic
data, the value must be an odd value between 1 and twice the length
of source-string plus one.
The argument can also be a character string or graphic string data type. The string argument is implicitly cast to a DECFLOAT(34) data type which is then assigned to an INTEGER.
- length
- An expression that specifies the length of the string
to replace in source-string starting at start. length must
be an expression that returns a value of the built-in INTEGER data
type. length is expressed in the string
unit specified, and the value must be in the range of 0 to the length
of source-string. If OCTETS is specified
and the result is graphic data, length must
be even and be between 0 and twice the length of source-string.
Not specifying length is equivalent to specifying
a value of 1, except when OCTETS is specified and the result is graphic
data, in which case, not specifying length is
equivalent to specifying a value of 2.
The argument can also be a character string or graphic string data type. The string argument is implicitly cast to a DECFLOAT(34) data type which is then assigned to an INTEGER.
- insert-string
- An expression that specifies the string to be inserted
into the source string, starting at the position identified by start.
The expression must return a value that is a built-in character string,
graphic string, or binary string data type that is not a LOB.
source-string and insert-string must have compatible data types.
The argument can also be a numeric data type. The numeric argument is implicitly cast to a VARCHAR data type.
- CODEUNITS16, CODEUNITS32, or OCTETS
- Specifies the units that are used to express start and length.
If source-string is a character string that
is defined as bit data, CODEUNITS16 and CODEUNITS32 cannot be specified.
If source-string is a graphic string, OCTETS
cannot be specified. If source-string is
a binary string, CODEUNITS16, CODEUNITS32, and OCTETS cannot be specified.
If a string unit is not explicitly specified, the data type of the result determines the unit that is used:
- If the result is a graphic string, a string unit is two bytes. For ASCII and EBCDIC data, this corresponds to a double byte character. For Unicode, this corresponds to a UTF-16 code point.
- Otherwise, a string unit is a byte.
- CODEUNITS16
- Specifies that start and length are expressed in terms of 16-bit UTF-16 code units.
- CODEUNITS32
- Specifies that start and length are expressed in terms of 32-bit UTF-32 code units.
- OCTETS
- Specifies that start and length are expressed in terms of bytes.
For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String unit specifications.
If source-string and insert-string have different CCSID sets, insert-string (the string to be inserted) is converted to the CCSID of source-string (the source string).
The encoding scheme of the result is the same as source-string. The data type of the result of the function depends on the data type of source-string and insert-string:
- VARCHAR if source-string is a character
string. The CCSID of the result depends on the arguments:
- If either source-string or insert-string is character bit data, the result is bit data.
- If source-string is SBCS Unicode data and insert-string is not SBCS Unicode data, the CCSID of the result is the mixed CCSID for Unicode data.
- If both source-string and insert-string are SBCS Unicode data, the CCSID of the result is the CCSID for SBCS Unicode data.
- Otherwise, the CCSID of the result is the mixed CCSID that corresponds to the CCSID of source-string. However, if the input is EBCDIC or ASCII and there is no corresponding system CCSID for mixed, the CCSID of the result is the CCSID of source-string.
- VARGRAPHIC if source-string is a graphic. The CCSID of the result is the same as the CCSID of source-string.
- VARBINARY if source-string and insert-string are both binary strings.
The length attribute of the result depends on the arguments:
- If start and length are
constants, the length attribute of the result is:
where:L1 - MIN((L1 - V2 + 1), V3) + L4
- L1 is the length attribute of source-string
- V2 is the value of start
- V3 is the value of length
- L4 is the length attribute of insert-string
- Otherwise, the length attribute of the result is the length attribute of source-string plus the length attribute of insert-string. In this case, the length attribute of source-string plus the length attribute of insert-string must not exceed 32704 for a VARCHAR result or 16352 for a VARGRAPHIC result.
If CODEUNITS16 or CODEUNITS32 is specified, the insert operation is performed on a Unicode version of the data. If needed, the data is converted to an intermediate form in order to evaluate the function. If an intermediate form is used, the actual length of the result depends on the original data (source-string and insert-string), and the representation of that data in Unicode. See Determining the length attribute of the final result for more information on how to calculate the length attribute of the result string.
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 occurs.
The result can be null; if any argument is null, the result is the null value.
SELECT CHAR(INSERT('INSERTING',4,2,'IS'),10),
CHAR(INSERT('INSERTING',4,0,'IS'),10),
CHAR(INSERT('INSERTING',4,2,''),10)
FROM SYSIBM.SYSDUMMY1;
This example returns 'INSISTING ',
'INSISERTIN', and 'INSTING ' SELECT CHAR(INSERT('INSERTING',1,0,'XX'),10),
CHAR(INSERT('INSERTING',1,1,'XX'),10),
CHAR(INSERT('INSERTING',1,2,'XX'),10),
CHAR(INSERT('INSERTING',1,3,'XX'),10)
FROM SYSIBM.SYSDUMMY1;
This example returns 'XXINSERTIN',
'XXNSERTING', 'XXSERTING ', and 'XXERTING ' SELECT CHAR(INSERT('ABCABC',7,0,'XX'),10)
FROM SYSIBM.SYSDUMMY1;
This example returns 'ABCABCXX '. SELECT VARCHAR(INSERT('Hegelstraße',10,1,'ss'),15)
FROM SYSIBM.SYSDUMMY1;
This example returns 'Hegelstrasse'.