INSERT( ) function
Returns the original string with specified text inserted at a specific byte location.
Syntax
INSERT(string, insert_text, location)
Parameters
| Name | Type | Description |
|---|---|---|
| string | character | The field, expression, or literal value to insert the text into. |
| insert_text | character | The text to insert. |
| location | numeric | The character position at which to insert insert_text into the string. |
Output
Character.
Examples
Basic examples
Returns "aXXXbcde":
INSERT("abcde", "XXX", 2)
Returns "XXXabcde":
INSERT("abcde", "XXX", 0)
Returns "abcdeXXX", with "XXX" inserted at byte position 6 instead of 8, because "abcde" is only 5 bytes long::
INSERT("abcde", "XXX", 8)
Remarks
How it works
The INSERT( ) function inserts specified characters or spaces into a character string, beginning at a specified position in the string.
When to use INSERT( )
Use INSERT( ) 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." In the first file, you can use INSERT( ) to insert a hyphen (-) in position 3.
Location guidelines
- If the location value is greater than the length of string, the insert_text value is inserted at the end of the string.
- If location is 0 or 1, insert_text is inserted at the beginning of the string.
Inserting double quotation marks
If you specify double quotation marks in insert_text, you must enclose them in single quotation marks.
For example: '"'
