INSERT
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 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 0 or greater.
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:
where:L1 - MIN((L1-V2 + 1), V3) + L4
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.
This example returns 'INSISTING ', 'INSISERTIN', and 'INSTING '.SELECT INSERT('INSERTING', 4, 2, 'IS'), INSERT('INSERTING', 4, 0, 'IS'), INSERT('INSERTING', 4, 2, '') FROM SYSIBM.SYSDUMMY1
- 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).
This example returns 'XXINSERTIN', 'XXNSERTING', 'XXSERTING ', and 'XXERTING '.SELECT INSERT('INSERTING', 1, 0, 'XX'), INSERT('INSERTING', 1, 1, 'XX'), INSERT('INSERTING', 1, 2, 'XX'), INSERT('INSERTING', 1, 3, 'XX') FROM SYSIBM.SYSDUMMY1
- 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).
This example returns 'ABCABCXX '.SELECT INSERT('ABCABC', 7, 0, 'XX') FROM SYSIBM.SYSDUMMY1