MSCEWI2050
Translate function has different behavior in certain cases

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:
1
SELECT OTRANSLATE('TestString', NULL, 'm5'); -- returns 'TestString'
Copied!
Snowflake:
1
SELECT TRANSLATE('TestString', NULL, 'm5'); // returns NULL
Copied!

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:
1
SELECT OTRANSLATE('hi123goodbye', 'io', 'a0F5');
2
-- replaces all i with a and all o for 0 as expected, F and 5 are ignored
Copied!
Snowflake:
1
SELECT TRANSLATE('hi123goodbye', 'io', 'a0F5');
2
// Fails with error String '(target alphabet)' is too long and would be truncated
Copied!

Example code

Input code:

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

Output code:

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

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.
    For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.
Last modified 1mo ago