Fix Names in a Table - Problem

You are working with a Users database table that has inconsistent name formatting. The names contain a mix of uppercase and lowercase letters, making the data look unprofessional and inconsistent.

Your task is to standardize all names so that only the first character is uppercase and the rest are lowercase (proper case formatting).

Column NameType
user_idint
namevarchar

user_id is the primary key for this table.

Goal: Transform names like "aLiCe", "bOB", or "CHARLIE" into proper case format: "Alice", "Bob", "Charlie".

Output: Return the result table ordered by user_id in ascending order.

Input & Output

example_1.sql — Basic Example
$ Input: Users table: | user_id | name | |---------|-------| | 1 | aLice | | 2 | bOB | | 3 | charlie |
› Output: | user_id | name | |---------|----------| | 1 | Alice | | 2 | Bob | | 3 | Charlie |
💡 Note: Each name is transformed to have only the first character uppercase and the rest lowercase, maintaining the original user_id order.
example_2.sql — Mixed Case Names
$ Input: Users table: | user_id | name | |---------|----------| | 1 | JOHN | | 2 | mArY | | 3 | dAvId |
› Output: | user_id | name | |---------|-------| | 1 | John | | 2 | Mary | | 3 | David |
💡 Note: All uppercase and mixed case names are properly formatted with only the first letter capitalized.
example_3.sql — Single Character Names
$ Input: Users table: | user_id | name | |---------|------| | 1 | a | | 2 | B | | 3 | c |
› Output: | user_id | name | |---------|------| | 1 | A | | 2 | B | | 3 | C |
💡 Note: Single character names are handled correctly, ensuring they are uppercase.

Constraints

  • 1 ≤ user_id ≤ 106
  • 1 ≤ name.length ≤ 100
  • name consists of only lowercase and uppercase English letters
  • The result should be ordered by user_id in ascending order

Visualization

Tap to expand
Database Name Standardization ProcessOriginal Data1 | aLice2 | bOB3 | CHARLIESQL TransformationUPPER(SUBSTRING(name,1,1))LOWER(SUBSTRING(name,2))CONCAT(upper_first, lower_rest)Clean Result1 | Alice2 | Bob3 | CharlieStep-by-Step Process1. SELECT user_id, CONCAT(...) AS name2. UPPER(SUBSTRING(name, 1, 1)) → First letter uppercase3. LOWER(SUBSTRING(name, 2)) → Rest lowercase4. CONCAT combines both parts5. ORDER BY user_id → Sorted output
Understanding the Visualization
1
Identify the Problem
Names in the database have mixed capitalization patterns
2
Extract First Character
Use SUBSTRING to get the first letter of each name
3
Transform Characters
Apply UPPER to first char, LOWER to remaining chars
4
Combine Results
Use CONCAT to merge the transformed parts
5
Sort Output
Order results by user_id for consistent output
Key Takeaway
🎯 Key Insight: SQL string functions can efficiently transform text data in bulk, making database cleanup operations fast and reliable

Related Problems

Asked in
Amazon 25 Microsoft 18 Google 15 Meta 12
26.8K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen