OTRANSLATE FUNCTION IN TERADATA
In this article, we will learn about OTRANSLATE FUNCTION IN TERADATA
Returns source_string with every occurrence of each character in from_string replaced with the corresponding character in to_string.
the name of the database where the function is located.
a character string or string expression.
If source_string is NULL, the function returns NULL.
a string of characters that will be replaced in source_string.
If from_string is NULL, the function returns source_string.
a string of characters that replaces the characters specified by from_string.
If to_string is:
- NULL is NULL the function returns NULL.
- An empty string, the corresponding character in from_string is removed.
Argument Types and Rules
Expressions passed to this function must have one of the following data types: CHAR or VARCHAR
Note: The UDF implicit type conversion rules are more restrictive than the implicit type conversion rules normally used by Teradata Database. If an argument cannot be converted to the required data type following the UDF implicit conversion rules, it must be explicitly cast.
The result data type is VARCHAR. The character set is the same as that of the source_string argument.
If the first character in from_string occurs in the source_string, all occurrences of it are replaced by the first character in to_string. This repeats for all characters in from_string and for all characters in from_string. The replacement is performed character-by-character, that is, the replacement of the second character is done on the string resulting from the replacement of the first character.
If from_string contains more characters than to_string, the extra characters are removed from the source_string. If an extra character was involved in a mapping, it is not removed, just ignored.
If from_string contains fewer characters than to_string, the extra characters in to_string have no effect.
If the same character occurs more than once in from_string, only the replacement character from the to_string corresponding to the first
The following query returns the string ‘TD14.0 is the current database version’. The occurrence in source_string of the character in from_string (‘3’) is replaced by the character in to_string (‘4’).
SELECT OTRANSLATE(‘TD13.0 is the current database version’,’3′, ‘4’);
In the following query, the characters ‘T’ and ‘h’ are replaced with ‘S’ and ‘p’, resulting in the string ‘Spin and Spick’. Next, the extra character ‘k’ in the from_string (where there is no corresponding character in the to_string) is removed from the source_string. The resulting string is ‘Spin and Spic’.
SELECT OTRANSLATE(‘Thin and Thick’, ‘Thk’, ‘Sp’);
In the following query, the characters ‘T’ and ‘h’ are replaced with ‘S’ and ‘p’. The character ‘T’ occurs twice in the from_string, but only the replacement character in the to_string that corresponds to the first occurrence of ‘T’ is used. That is, only ‘S’ is used to replace ‘T’. Next, the character ‘k’ is replaced with ‘x’, and the extra characters ‘y’ and ‘z’ in the to_string are ignored. The resulting string is ‘Spin and Spicx’.
SELECT OTRANSLATE(‘Thin and Thick’, ‘ThTk’, ‘Sptxyz’);
The following query returns the source_string unchanged since from_string is NULL.
SELECT OTRANSLATE(‘Thin and Thick’, NULL, ‘Spt’);