INSERT

>>-INSERT--(--source-string--,--start--,--length--,--insert-string--)-><
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 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
on 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 point 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 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
on 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 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.
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 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 '.
- 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 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
'.
- 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 CHAR(INSERT('ABCABC', 7, 0, 'XX'), 10)
FROM SYSIBM.SYSDUMMY1
This example returns 'ABCABCXX '.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.