The TRANSLATE function allows you to translate (replace) a specific character within a data string with another character. This function examines the data string and replaces all occurrences of a specific character with a user specified replacement character.
Category
String
Syntax
TRANSLATE(data_string, original_character, replacement_character)
| Parameter | Description |
|---|---|
| data_string | The source data string in character format. The data string can be a field from a source datastore, a variable, a constant or the result of another Function. |
| original_character | The character that, if found, is to be replaced. |
| replacement_character | This parameter specifies the character that will be used to replace the original character in the source data string. |
Example 1
Replace all of the spaces within a source data string with an asterisk (*). Map the result to target field TRANS_OUT.
TRANS_OUT = TRANSLATE ('THIS IS A TRANSLATE TEST', ' ', '*')
Returns the value THIS*IS*A*TRANSLATE*TEST and maps the result to the target field.
Example 2
Replace any occurrence of the hex value 0A with a hex 40 (space) using the X2C function. Map the result to target field TRANS_OUT.
TRANS_OUT = TRANSLATE (source_string, C2X('0A'), C2X('40'))