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.

>>-INSERT--(--source-string--,--start--,--length--,--insert-string--)-><
- source-string
- An expression that specifies the source string. The source-string may
be any built-in numeric or string expression. It must be compatible
with the insert-string. For more information about data type
compatibility, see Assignments and comparisons. A numeric
argument is cast to a character string before evaluating the function.
For more information about converting numeric to a character string,
see VARCHAR. The actual length of
the string must be greater than zero.
- start
- An expression that returns a built-in BIGINT, INTEGER, or SMALLINT
data type. The integer specifies the starting character within source-string where
the deletion of characters 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.
- length
- An expression that returns a built-in BIGINT, INTEGER, or SMALLINT
data type. The integer specifies the number of characters that are
to be deleted from source-string, starting
at the character position identified by start.
The value of the integer must be in the range of 0 to the length of source-string.
- insert-string
- An expression that specifies the string to be inserted into source-string,
starting at the position identified by start.
The insert-string may be any built-in numeric
or string expression. It must be compatible with the source-string.
For more information about data type compatibility, see Assignments and comparisons. A numeric argument is cast to a character
string before evaluating the function. For more information about
converting numeric to a character string, see VARCHAR. The actual length of the string
must be greater than zero.
The data type of the result of the function depends on
the data type of the first and fourth arguments. The result data type
is the same as if the two arguments were concatenated except that
the result is always a varying-length string. For more information
see Conversion rules for operations that combine strings.
The length attribute of the result depends on the arguments:
- If start and length are
constants, the length attribute of the result is:
L1 - MIN((L1-V2 + 1), V3) + L4
where: L1 is the length attribute of source-string
V2 depends on the encoding schema of source-string:
- If the source-string is UTF-8, the value MIN(L1+1,start*3)
- If the source-string is mixed data, the value MIN(L1+1,(start-1)*2.5+4)
- Otherwise, 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.
If the length attribute of the result exceeds the maximum
for the result data type, an error is returned.
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 result data type, an error is returned.
If any
argument can be null, the result can be null; if any argument is null,
the result is the null value.
The CCSID of the result is determined
by the CCSID of source-string and insert-string.
The resulting CCSID is the same as if the two arguments were concatenated.
For more information, see Conversion rules for operations that combine strings.
Examples
- The following example shows how the string 'INSERTING' can be
changed into other strings. The use of the CHAR function limits the
length of the resulting string to 10 characters.
SELECT INSERT('INSERTING', 4, 2, 'IS'),
INSERT('INSERTING', 4, 0, 'IS'),
INSERT('INSERTING', 4, 2, '')
FROM SYSIBM.SYSDUMMY1
This example returns 'INSISTING ', 'INSISERTIN', and 'INSTING
'.
- The previous example demonstrated how to insert text into the
middle of some text. This example shows how to insert text before
some text by using 1 as the starting point (start).
SELECT INSERT('INSERTING', 1, 0, 'XX'),
INSERT('INSERTING', 1, 1, 'XX'),
INSERT('INSERTING', 1, 2, 'XX'),
INSERT('INSERTING', 1, 3, 'XX')
FROM SYSIBM.SYSDUMMY1
This example returns 'XXINSERTIN', 'XXNSERTING', 'XXSERTING
', and 'XXERTING '.
- The following example shows how to insert text after some text.
Add 'XX' at the end of string 'ABCABC'. 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
This example returns 'ABCABCXX '.