INSERT( ) function

Returns the original string contents with a specified string inserted in a specified location.


INSERT(string, insert_text, location)



Character. The value to insert the text into.


Character. The value to insert into string.


Numeric. The byte position where the insert_text value will be inserted into string.




This function inserts characters or spaces into a character string, in any position. If the location parameter value is greater than the length of the string parameter, the insert_text parameter value is inserted at the end of string. If location is 0 or 1, insert_text is inserted at the beginning of string. If you are specifying double quotes in insert_text, you must enclose them in single quotes (i.e., ‘”’).

The INSERT( ) function can be used to normalize data for formatting, for duplicate matching, and for the JOIN and DEFINE RELATION commands, which require identical fields. For example, part numbers in one file may be in the format “12345”, and in another file, “12-345.” You can use INSERT( ) to insert a hyphen (-) in position 3.


Example Return value

INSERT("abcde", "XXX", 2)


INSERT("abcde", "XXX", 8)


INSERT("abcde", "XXX", 0)


INSERT(“6543”, “BCD”, 6)


Because “6543” is only 4 bytes long, “BCD” is inserted at byte position 5 instead of 6.

(C) 2015 ACL Services Ltd. All Rights Reserved.