Product Documentation

SQL Reference Guide

Previous Topic

Next Topic

INSERT function (ODBC compatible)




The scalar function INSERT returns a character string where length characters have been deleted from string_exp1 beginning at start and string_exp2 has been inserted into string_exp1, beginning at start. The above operation will be performed only if both the arguments belong to the same character set (exceptions are shown below in the Notes section).


SELECT INSERT(name,2,4,'xx')

FROM customer

WHERE name = 'Goldman';



1 record selected


  • The string_exp can be of fixed length or variable length character types.
  • The start and length can be of the type INTEGER, SMALLINT, TINYINT or BIGINT.
  • The string_exp2 has to belong to the string_exp1’s character set.
  • If one of the arguments is a literal and the other one a field reference, the operation is possible only if the literal is convertible to the character set of the field reference. Otherwise an error is returned.
  • The result string is of the type string_exp1 and will belong to same character set as string_exp1.
  • If any of the argument expression evaluates to a null, the result is a null.
  • If start is negative or zero, the result string evaluates to a null.
  • If length is negative, the result evaluates to a null.