MSCEWI2050

Translate function has different behavior in certain cases

This is a deprecated version of the SnowConvert documentation, please visit the official site HERE.

Severity

Low

Description

In most cases, OTRANSLATE Teradata function and Snowflake TRANSLATE show the same behavior.

However, the following two cases should be considered:

NULL parameters

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:

Teradata:

SELECT OTRANSLATE('TestString', NULL, 'm5'); -- returns 'TestString'

Snowflake:

SELECT TRANSLATE('TestString', NULL, 'm5'); // returns NULL

Length of the third parameter greater than the length of the second parameter

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:

Teradata:

SELECT OTRANSLATE('hi123goodbye', 'io', 'a0F5'); 
-- replaces all i with a and all o for 0 as expected, F and 5 are ignored

Snowflake:

SELECT TRANSLATE('hi123goodbye', 'io', 'a0F5');
// Fails with error String '(target alphabet)' is too long and would be truncated

Example code

Input code:

SELECT OTRANSLATE('HelloWorld!', 'lo!', '10?');

Output code:

SELECT
TRANSLATE('HelloWorld!', 'lo!', '10?') /*** MSC-WARNING - MSCEWI2050 - TRANSLATE FUNCTION HAS DIFFERENT BEHAVIOR IN CERTAIN CASES ***/;

Recommendations

• 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.

• If you need more support, you can email us at [email protected]