Fix Names in a Table

Fix Names in a Table

1. What is this problem about?

The Fix Names in a Table interview question is a SQL string formatting task. You are given a Users table with a name column. The names are stored inconsistently (e.g., "aLICE", "BOB"). You need to write a query that returns the names formatted such that only the first character is uppercase and the rest are lowercase.

2. Why is this asked in interviews?

Companies like Microsoft and Amazon use the Fix Names coding problem to test basic string manipulation functions in SQL. Data normalization is a critical part of database management, and being able to clean strings using UPPER, LOWER, SUBSTRING, and CONCAT is a foundational skill.

3. Algorithmic pattern used

This problem follows the SQL String Concatenation pattern.

1. Extract First Char: Use SUBSTR(name, 1, 1) and apply UPPER().
2. Extract Remaining: Use SUBSTR(name, 2) and apply LOWER().
3. Combine: Use CONCAT() or the || operator to join the two parts.
4. Finalize: Order the results by user ID as requested.

4. Example explanation

• Input: aLICE
• UPPER(SUBSTR('aLICE', 1, 1)) -> 'A'
• LOWER(SUBSTR('aLICE', 2)) -> 'lice'
• Result: 'Alice'
• Input: BOB
• UPPER('B') -> 'B'
• LOWER('OB') -> 'ob'
• Result: 'Bob'

5. Common mistakes candidates make

• Off-by-one: Mistakes in the SUBSTR indices (some SQL dialects are 1-indexed, others are 0-indexed).
• Missing Lowercase: Forgetting to lowercase the tail of the string, resulting in "Alice" remaining "ALICE".
• Concatenation syntax: Using the wrong operator for the specific database (e.g., + in SQL Server vs || in PostgreSQL).

6. Interview preparation tip

Familiarize yourself with string functions in major SQL dialects (MySQL, PostgreSQL, T-SQL). Knowing that SUBSTRING and LENGTH exist is usually enough, but knowing the exact syntax for the requested dialect shows you are well-prepared.