SnowPro Associate: Platform Question 321
Single answer● TRANSLATE functionA retail company loads product codes from multiple regional systems into Snowflake. Some source systems store separators inconsistently, such as hyphens (-), forward slashes (/), and spaces, but analytics teams want a normalized code with all three characters removed. For example, the value 'AB-12 /C 3' should become 'AB12C3'. A data engineer wants a simple SQL expression that performs this cleanup in a SELECT statement without using regular expressions. Which approach should the engineer use?
- A
Use TRANSLATE(product_code, '-/ ', '')
- B
Use REPLACE(product_code, '-/ ', '')
- C
Use TRANSLATE(product_code, '-/ ', NULL)
- D
Use TRANSLATE(product_code, '-/ ', '___')
Show answer and explanation
Correct answer: A
Explanation
Snowflake's TRANSLATE function is useful when a developer needs character-by-character substitution or removal. It differs from REPLACE because REPLACE operates on substrings, while TRANSLATE operates on individual characters based on positional mapping between the second and third arguments. A key behavior is that when the target alphabet is shorter than the source alphabet, unmatched source characters are omitted from the output. This makes TRANSLATE a practical choice for removing several distinct unwanted characters in a single expression, such as punctuation or separators in product codes. In this scenario, TRANSLATE(product_code, '-/ ', '') removes each listed character wherever it appears. This aligns with Snowflake documentation for TRANSLATE and with common data standardization practices used during query-time cleansing.
- A. Correct.
Correct. In Snowflake, TRANSLATE maps characters in the source alphabet to characters in the target alphabet by position. If the target string is shorter than the source string, extra source characters are removed from the result. Therefore, TRANSLATE(product_code, '-/ ', '') removes hyphens, forward slashes, and spaces in a single call, which fits the requirement to normalize values without regular expressions.
- B. Incorrect.
Incorrect. REPLACE searches for an entire substring, not individual characters from a set. REPLACE(product_code, '-/ ', '') would only remove the exact three-character sequence '-/ ' if it appeared contiguously, which does not solve the scenario where separators occur independently throughout the string.
- C. Incorrect.
Incorrect. Passing NULL as an argument to TRANSLATE results in NULL, because Snowflake string functions generally return NULL if any argument is NULL. This would not remove characters; it would nullify the output.
- D. Incorrect.
Incorrect. This maps each separator character to an underscore rather than removing it. The source characters '-', '/', and space would each be translated to '_', producing something like 'AB_12__C_3', which does not meet the requirement.