String & Binary Functions (General)
Replaces a substring of the specified length, starting at the specified position, with a new string or binary value.
This function should not be confused with the INSERT DML command.
INSERT( <base_expr>, <pos>, <len>, <insert_expr> )
The string or BINARY expression for which you want to insert/replace characters.
The offset at which to start inserting characters. This is 1-based, not 0-based. In other words, the first character in the string is considered to be at position 1, not position 0. For example, to insert at the beginning of the string, set
Valid values are between 1 and one more than the length of the string (inclusive).
posto one more than the length of the string makes the operation equivalent to an append. (This also requires that the
lenparameter be 0 because you should not try to delete any characters past the last character.)
The number of characters (starting at
pos) that you want to replace. Valid values range from 0 to the number of characters between
posand the end of the string. If this is 0, it means add the new characters without deleting any existing characters.
The string to insert into the
base_expr. If this string is empty, and if
lenis greater than zero, then effectively the operation becomes a delete (some characters are deleted, and none are added).
insert_exprshould be the same data type; either both should be string (e.g. VARCHAR) or both should be binary.
If any of the arguments are NULL, the returned value is NULL.
Returns a string or BINARY that is equivalent to making a copy of
len characters starting at
pos, and then inserting
Note that the original input
base_expr is not changed; the function
returns a separate (modified) copy.
This is a simple example:
SELECT INSERT('abc', 1, 2, 'Z') as STR; +-----+ | STR | |-----| | Zc | +-----+
This example shows that the length of the replacement string can be different from the length of the substring being replaced:
SELECT INSERT('abcdef', 3, 2, 'zzz') as STR; +---------+ | STR | |---------| | abzzzef | +---------+
This shows what happens when the replacement string is empty (the function deletes the specified number of characters starting at the start position, and does not add any characters):
SELECT INSERT('abc', 2, 1, '') as STR; +-----+ | STR | |-----| | ac | +-----+
INSERT as an append operation, by adding characters immediately
after the last character in the original string:
SELECT INSERT('abc', 4, 0, 'Z') as STR; +------+ | STR | |------| | abcZ | +------+
The following all return NULL because at least one of the arguments is NULL:
SELECT INSERT(NULL, 1, 2, 'Z') as STR; +------+ | STR | |------| | NULL | +------+SELECT INSERT('abc', NULL, 2, 'Z') as STR; +------+ | STR | |------| | NULL | +------+SELECT INSERT('abc', 1, NULL, 'Z') as STR; +------+ | STR | |------| | NULL | +------+SELECT INSERT('abc', 1, 2, NULL) as STR; +------+ | STR | |------| | NULL | +------+