Fix Names in a Table | LeetCode 1667

Welcome to Question 44 of the SQL Interview Series. In this chapter, we explore advanced string manipulation functions, regular expressions, and clauses in MySQL by solving LeetCode 1667: Fix Names in a Table. This problem demonstrates how to clean, format, and normalize messy string data using built-in string functions.

1. Problem Statement & Database Schema

We are given a database table named Users that stores user identification and names. The requirement is to format the names so that only the first character is uppercase and all remaining characters are lowercase (proper title case). Finally, the result table must be ordered by user_id.

Schema Definition & Constraints

Table Name: Users
Columns:
- user_id (int): The primary key containing unique identifiers for users.
- name (varchar): Contains a mix of lower-case and upper-case characters.

Example Input and Expected Output

Input (Users Table):

user_id	name
1	aLiS
2	bOB

Expected Output:

user_id	name
1	Alis
2	Bob

2. Core Mental Model & Real-Life Analogy

Real-Life Analogy: Editing a Printed Name Tag

Imagine you are hand-correcting name tags for an event where participants carelessly typed their names in mixed cases (e.g., aLiS or bOB).

You take a pair of scissors and split each name badge right after the first letter.
You take the first piece (the single initial letter), dip it in uppercase ink, and stamp it so it is capitalized.
You take the remaining piece (all letters after the first), dip it in lowercase ink, and smooth it out so everything is lowercase.
Finally, you glue the two pieces back together to form a clean, properly formatted name tag.

Structure & Concept Flow

Original String: "aLiS"

+---> Split into: [ "a" ] and [ "LiS" ]

+---> Transform: Upper("a") -> "A" and Lower("LiS") -> "lis"

+---> Concatenate: "A" + "lis" = "Alis"

3. String Function Toolkit in MySQL

To implement our physical analogy in SQL, we rely on five essential string and utility functions:

Function Category	Function Name	Syntax Example	Description / Behavior
Extraction	`LEFT()`	`LEFT(col, n)`	Extracts `n` characters from the left side of the string.
Extraction	`RIGHT()`	`RIGHT(col, n)`	Extracts `n` characters from the right side of the string.
Case Conversion	`UPPER()`	`UPPER(str)`	Converts all characters in the string to uppercase.
Case Conversion	`LOWER()`	`LOWER(str)`	Converts all characters in the string to lowercase.
Utility	`LENGTH()`	`LENGTH(str)`	Returns the total byte/character length of the string.
Combination	`CONCAT()`	`CONCAT(str1, str2)`	Joins two or more strings together into a single string.

4. Step-by-Step Query Evolution

Step 4.1: Selecting the Table and Ordering

We begin by querying the Users table and ensuring the output is sorted by user_id in ascending order as specified in the problem statement.

SQL

SELECT user_id, name
FROM Users
ORDER BY user_id;

Step 4.2: Isolating the First Character

We know the first character is always at the absolute beginning of the string. We use LEFT(name, 1) to grab it, and wrap it in UPPER() to force capitalization.

SQL

-- Extracts and capitalizes the first letter
UPPER(LEFT(name, 1))

Step 4.3: Isolating and Lowercasing the Remaining Characters

Unlike the first character, the length of the remaining part of the name is variable (e.g., alis vs ob). Therefore, we calculate the remaining length dynamically using total LENGTH(name) minus 1. We then extract it using RIGHT() and convert it using LOWER().

SQL

-- Extracts remaining characters and converts to lowercase
LOWER(RIGHT(name, LENGTH(name) - 1))

Conceptually: If name is "bOB", LENGTH(name) is 3. 3 - 1 = 2. RIGHT("bOB", 2) grabs "OB", and LOWER("OB") turns it into "ob".

5. Complete Final SQL Solution

By combining our transformation logic inside a CONCAT() function and projecting user_id, we arrive at our final production-ready query.

SQL

SELECT 
    user_id,
    CONCAT(
        UPPER(LEFT(name, 1)), 
        LOWER(RIGHT(name, LENGTH(name) - 1))
    ) AS name
FROM Users
ORDER BY user_id;

Flow Walkthrough of the Query Execution

Structure & Concept Flow

Users Table Row (user_id = 1, name = "aLiS")

|-- 1. LEFT(name, 1) extracts "a" --------> UPPER("a") -> "A"

|-- 2. LENGTH(name) - 1 evaluates (4 - 1 = 3)

|-- 3. RIGHT(name, 3) extracts "LiS" ----> LOWER("LiS") -> "lis"

CONCAT("A", "lis") executes

Result column name becomes "Alis"

ORDER BY user_id sorts the final result set

6. Summary and Key Takeaways

Problem Decomposition: Complex string formatting tasks are best broken down into smaller sub-problems (handling the head of the string separately from the tail).
Head Extraction (LEFT): Use LEFT(column, 1) to reliably grab the very first character of any string regardless of its total length.
Tail Extraction (RIGHT + LENGTH): Use RIGHT(column, LENGTH(column) - 1) to dynamically isolate all trailing characters when the string length is variable.
Case Control: Combine UPPER() on the prefix and LOWER() on the suffix to achieve precise title-casing.
Concatenation (CONCAT): Re-stitch modified substring pieces back together into a single unified column value.
Ordering: Always apply ORDER BY clauses matching the exact sorting requirements specified in interview prompts (ascending by default, or appending DESC if explicitly requested).