SnowPro Associate: Platform Question 322
Single answer● TRANSLATE functionA data engineer is standardizing customer phone numbers stored in a VARCHAR column named PHONE_RAW before loading them into a curated table. The raw values contain inconsistent formatting such as parentheses, hyphens, spaces, and plus signs, for example: '+1 (415) 555-0199' and '(212) 555 0100'. The engineer wants a simple SQL expression that removes only these formatting characters while leaving all digits unchanged. Which approach best meets this requirement in Snowflake?
- A
Use TRANSLATE(PHONE_RAW, '()+- ', '') to remove the formatting characters in a single function call.
- B
Use REPLACE(PHONE_RAW, '()+- ', '') because REPLACE removes each individual character found in the search string.
- C
Use TRANSLATE(PHONE_RAW, '0123456789', '') to keep only punctuation and remove digits, then cast back to VARCHAR.
- D
Use SUBSTR(PHONE_RAW, 1, LEN(PHONE_RAW) - 5) because formatting characters are typically stored at the end of phone numbers.
Show answer and explanation
Correct answer: A
Explanation
The Snowflake TRANSLATE function is useful when you need to replace or remove individual characters based on a one-to-one positional mapping. A key behavior is that if the second argument lists more characters than the third argument, the unmatched extra characters are removed from the input. That makes TRANSLATE ideal for stripping a known set of single-character symbols such as phone number formatting marks. By contrast, REPLACE works on whole substrings, not character sets, so it is not the best choice for removing multiple different single characters in one call. In real-world data cleansing, TRANSLATE is commonly used for lightweight character normalization, while more complex pattern-based cleansing may require REGEXP_REPLACE. This aligns with Snowflake string function behavior documented for TRANSLATE and REPLACE.
- A. Correct.
Correct. In Snowflake, TRANSLATE performs character-by-character substitution based on position. When the source character set is longer than the target character set, extra characters in the source are removed from the input string. Therefore, TRANSLATE(PHONE_RAW, '()+- ', '') removes any occurrences of '(', ')', '+', '-', and space while leaving digits and other non-listed characters unchanged. This is a practical way to strip a known set of single-character formatting symbols.
- B. Incorrect.
Incorrect. REPLACE in Snowflake replaces occurrences of one substring with another substring; it does not interpret the search string as a set of individual characters to remove independently. REPLACE(PHONE_RAW, '()+- ', '') would only remove the exact contiguous substring '()+- ' if it appeared as-is, which is not the case in typical phone number values.
- C. Incorrect.
Incorrect. This does the opposite of the stated requirement. TRANSLATE(PHONE_RAW, '0123456789', '') would remove digits, not preserve them. Also, there is no reason to cast back to VARCHAR because TRANSLATE already returns a string type. This distractor reflects a misunderstanding of which characters belong in the source argument.
- D. Incorrect.
Incorrect. SUBSTR truncates by character position and does not selectively remove punctuation characters from different positions in the string. Phone formatting characters can appear throughout the value, not just at the end. Also, Snowflake uses LENGTH rather than LEN as the standard string-length function name.