CompTIA Data+ (DA0-002)Data MiningMedium
A data analyst is preparing a dataset of product reviews for natural language processing. They notice that many reviews contain leading or trailing spaces, or multiple spaces between words (e.g., ' Great product! ' or 'Very good'). Which SQL string function should be used to clean up these extraneous spaces?
- ACONCAT()
- BSUBSTRING()
- CTRIM() and REPLACE()
- DLENGTH()
Show answer & explanationAnswer & explanation
Correct answer: C. TRIM() and REPLACE()
TRIM() is used to remove leading and trailing spaces from a string. To handle multiple spaces between words, a REPLACE() function can be nested or chained to replace double spaces with single spaces until no double spaces remain. This combination effectively cleans up all types of extraneous spaces.
Why the other options are wrong
- A. CONCAT() joins strings, not for removing spaces.
- B. SUBSTRING() extracts a part of a string, not for cleaning spaces.
- D. LENGTH() returns the length of a string, not for cleaning spaces.
SQL String Cleansing
SQL string cleansing involves using string functions to remove unwanted characters, correct formatting, and standardize text data for better analysis and consistency.
- Common tasks include removing spaces, converting case, and replacing characters.
- TRIM(), LTRIM(), RTRIM() handle leading/trailing spaces.
- REPLACE() and REGEXP_REPLACE() are powerful for internal string manipulation.
Memory trick: Trim the ends, Replace the middles!