SnowPro Associate: Platform Question 323
Single answer● TRANSLATE functionA retail company loads product codes from several legacy systems into Snowflake. Some codes contain separators that must be removed before downstream matching, for example: 'AB-12/34.CD'. The data engineering team wants a simple SQL expression that removes hyphens (-), slashes (/), and periods (.) from each code in a single function call without using regular expressions. Which expression best meets this requirement?
- A
TRANSLATE(product_code, '-/.', '')
- B
REPLACE(product_code, '-/.', '')
- C
TRANSLATE(product_code, '-/.', ' ')
- D
REGEXP_REPLACE(product_code, '[-/.]', '')
Show answer and explanation
Correct answer: A
Explanation
Snowflake's TRANSLATE function is designed for one-to-one character mapping. It is especially useful when multiple individual characters must be replaced or removed in a single call. A key behavior is that if the target alphabet string is shorter than the source alphabet string, any extra source characters are deleted from the result. That makes TRANSLATE(product_code, '-/.', '') an efficient way to strip those separators. By contrast, REPLACE works on substrings rather than positional character sets, so it cannot remove several different characters at once unless nested repeatedly. REGEXP_REPLACE is more flexible, but when the requirement is simple character removal without regex, TRANSLATE is the preferred function. This aligns with Snowflake SQL function documentation and common best practices for straightforward character normalization tasks.
- A. Correct.
Correct. In Snowflake, TRANSLATE performs character-by-character replacement based on positional mapping from the source character set to the target character set. If the target string is shorter than the source string, extra characters in the source string are removed. Therefore, TRANSLATE(product_code, '-/.', '') removes all hyphens, slashes, and periods in one call.
- B. Incorrect.
Incorrect. REPLACE substitutes one substring with another substring, not multiple different characters in a single positional mapping. REPLACE(product_code, '-/.', '') would only remove the exact substring '-/.' if it appeared contiguously, which does not solve the problem.
- C. Incorrect.
Incorrect. TRANSLATE maps each character in the second argument to the character in the same position of the third argument. Using three spaces as the third argument would replace '-', '/', and '.' with spaces, not remove them. This is a common misunderstanding when comparing TRANSLATE to trimming or deletion behavior.
- D. Incorrect.
Incorrect. REGEXP_REPLACE can remove these characters and would work technically, but the scenario explicitly asks for a solution without using regular expressions and seeks a simple single-function character-mapping approach. TRANSLATE is the better fit for this requirement.