Comment on page
Translate function has different behavior in certain cases
In most cases, OTRANSLATE Teradata function and Snowflake TRANSLATE show the same behavior.
However, the following two cases should be considered:
When the second parameter of OTRANSLATE function (the string containing the characters to replace) is NULL the function returns the original string. In Snowflake any NULL parameter sent to the function causes the result to be NULL. Example:
SELECT OTRANSLATE('TestString', NULL, 'm5'); -- returns 'TestString'
SELECT TRANSLATE('TestString', NULL, 'm5'); // returns NULL
When the third parameter (string with the new characters to replace) is longer than the second parameter, OTRANSLATE ignores the extra characters and performs the replacements as expected. In Snowflake this case is forbidden and will generate an error. Example:
SELECT OTRANSLATE('hi123goodbye', 'io', 'a0F5');
-- replaces all i with a and all o for 0 as expected, F and 5 are ignored
SELECT TRANSLATE('hi123goodbye', 'io', 'a0F5');
// Fails with error String '(target alphabet)' is too long and would be truncated
SELECT OTRANSLATE('HelloWorld!', 'lo!', '10?');
TRANSLATE('HelloWorld!', 'lo!', '10?') /*** MSC-WARNING - MSCEWI2050 - TRANSLATE FUNCTION HAS DIFFERENT BEHAVIOR IN CERTAIN CASES ***/;
- Try to include only the necessary amount of characters in the third parameter, they are ignored in Teradata anyway and cause errors in Snowflake.
- When passing expressions that evaluate to string to this function try to avoid expressions that can evaluate to NULL, at least in the second parameter.