SnowPro Associate: Platform Question 320
Single answer● TRANSLATE functionA data engineer is standardizing customer phone numbers stored in a VARCHAR column named RAW_PHONE. The values contain inconsistent formatting such as parentheses, spaces, periods, plus signs, and hyphens, for example: '+1 (415) 555-0199' and '44.20.7946.0958'. The engineer wants a simple SQL expression that removes only these formatting characters while preserving all digits and any other letters that might appear in unexpected records for later review. Which Snowflake expression best fits this requirement?
- A
TRANSLATE(RAW_PHONE, '()+-. ', '')
- B
REPLACE(RAW_PHONE, '()+-. ', '')
- C
REGEXP_REPLACE(RAW_PHONE, '[^0-9]', '')
- D
TRANSLATE(RAW_PHONE, '[^0-9]', '')
Show answer and explanation
Correct answer: A
Explanation
The best answer is TRANSLATE(RAW_PHONE, '()+-. ', ''). In Snowflake, TRANSLATE performs character-by-character substitution. A practical and important behavior is that if the target string has fewer characters than the source string, the extra source characters are omitted from the output. This makes TRANSLATE efficient for removing a known set of unwanted formatting characters such as punctuation and spaces. By contrast, REPLACE works on whole substrings, not character sets, and REGEXP_REPLACE is better when the pattern is variable or truly requires regex logic. Here, the scenario specifically requires removing only a known list of formatting symbols while preserving anything else for data-quality investigation, which is exactly where TRANSLATE is appropriate.
- A. Correct.
Correct. In Snowflake, TRANSLATE maps characters from the source alphabet to characters in the target alphabet. When the target string is shorter than the source string, extra source characters are removed from the result. Using TRANSLATE(RAW_PHONE, '()+-. ', '') removes each occurrence of '(', ')', '+', '-', '.', and space anywhere in the string, while preserving digits and any unexpected alphabetic characters.
- B. Incorrect.
Incorrect. REPLACE in Snowflake replaces exact substring matches, not a set of individual characters. This expression would only remove the literal contiguous substring '()+-. ' if it appeared exactly in that order, which is not how phone-number formatting appears in real data.
- C. Incorrect.
Incorrect. REGEXP_REPLACE(RAW_PHONE, '[^0-9]', '') would remove every non-digit character. Although that may appear useful for normalization, it does not meet the requirement to preserve unexpected letters for later review. For example, a value like '1-800-FLOWERS' would lose the letters.
- D. Incorrect.
Incorrect. TRANSLATE does not interpret regular expression patterns. The string '[^0-9]' would be treated as literal characters [, ^, 0, -, 9, ] to translate or remove, not as a regex meaning 'non-digit characters'. This is a common confusion between TRANSLATE and regex-based functions.