SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Functions and Expressions
HP NonStop SQL/MX Reference Manual—540440-003
9-66
INSERT Function
INSERT Function
The INSERT function returns a character string where a specified number of
characters within the character string have been deleted beginning at a specified start
position and then another character string has been inserted at the start position.
INSERT is an SQL/MX extension.
char-expr-1, char-expr-2
are SQL character value expressions (of data type CHAR or VARCHAR) that
specify two strings of characters. The character string char-expr-2 is inserted
into the character string char-expr-1. See Character Value Expressions on
page 6-37.
start
specifies the starting position start within char-expr-1 at which to start
deleting length number of characters. After the deletion, the character string
char-expr-2 is inserted into the character string char-expr-1, beginning at the
start position specified by the number start. The number start must be a value
greater than zero of exact numeric data type and with a scale of zero.
length
specifies the number of characters to delete from char-expr-1. The number
length must be a value greater than or equal to zero of exact numeric data type
and with a scale of zero. length must be less than or equal to the length of
char-expr-1.
Examples of INSERT
•
Suppose that your JOB table includes an entry for a sales representative. Use the
INSERT function to change SALESREP to SALES REP:
UPDATE persnl.job
SET jobdesc = INSERT (jobdesc, 6, 3, ' REP')
WHERE jobdesc = 'SALESREP';
Now check the row you updated:
SELECT jobdesc FROM persnl.job
WHERE jobdesc = 'SALES REP';
Job Description
------------------
SALES REP
--- 1 row(s) selected.
INSERT (char-expr-1, start, length, char-expr-2)