CompTIA Data+ (DA0-002)Data MiningMedium
A data quality team is reviewing a dataset where customer names are stored in various inconsistent formats (e.g., 'JOHN DOE', 'john doe', 'Johnathan Doe'). Before integrating this data into a master customer database, they need to apply a transformation to ensure all names follow a 'Proper Case' format (e.g., 'John Doe'). Which of the following SQL functions would be most effective for this specific transformation when applied to a `customer_name` column?
- ATRIM(customer_name)
- BUPPER(customer_name)
- CLOWER(customer_name)
- DINITCAP(customer_name) / PROPER(customer_name)
Show answer & explanationAnswer & explanation
Correct answer: D. INITCAP(customer_name) / PROPER(customer_name)
Functions like `INITCAP` (in Oracle/PostgreSQL) or `PROPER` (in some other SQL dialects or spreadsheet tools) are specifically designed to convert the first letter of each word in a string to uppercase and the rest to lowercase, achieving the 'Proper Case' format. This directly addresses the requirement for consistent capitalization.
Why the other options are wrong
- A. `TRIM()` removes leading and trailing whitespace, which is a different cleansing task and does not affect capitalization.
- B. `UPPER()` converts all characters to uppercase, which is not 'Proper Case'.
- C. `LOWER()` converts all characters to lowercase, which is not 'Proper Case'.
SQL String Functions (Case Conversion)
SQL functions used to manipulate the case of characters within a string, such as converting to uppercase, lowercase, or proper (initial capital) case.
- UPPER() converts all characters to uppercase.
- LOWER() converts all characters to lowercase.
- INITCAP() (or PROPER()) capitalizes the first letter of each word.
Memory trick: Strings can be shaped, like clay, with SQL's fine hand.