Fix Names in a Table | LeetCode | MSSQL

The Problem

Table: Users

The user_id is the primary key for this table. This table contains the ID and the name of the user. The name consists of only lowercase and uppercase characters.

The task is to write an SQL query to fix the names so that only the first character is uppercase and the rest are lowercase. The result table should be ordered by user_id.

Here's an example to better illustrate the problem:

Input

Users table:

Output

The Solution

In this post, we'll explore four different MSSQL solutions that solve the problem using different functions and approaches. Each solution demonstrates a unique approach, offering varying levels of complexity, readability, and performance.

Source Code 1

The first solution capitalizes the first character and changes the remaining characters to lowercase using the UPPER, LEFT, LOWER, and RIGHT functions:

SELECT
    user_id,
    UPPER(LEFT(name, 1)) + LOWER(RIGHT(name, LEN(name)-1)) [name]
FROM Users
ORDER BY user_id

This code's runtime is 1977ms, beating 29.95% of other submissions. Here's the performance snapshot:

Source Code 2

The second solution uses the CONCAT function in conjunction with the UPPER, LEFT, LOWER, and STUFF functions:

SELECT
    user_id,
    CONCAT(UPPER(LEFT(name, 1)), LOWER(STUFF(name, 1, 1, ''))) [name]
FROM Users
ORDER BY user_id

This solution's runtime is 3185ms, beating 5.10% of other submissions. Here's the performance snapshot:

Source Code 3

The third solution changes the approach slightly, using the SUBSTRING function instead of RIGHT or STUFF:

SELECT
    user_id,
    UPPER(SUBSTRING(name, 1, 1)) + LOWER(SUBSTRING(name, 2, LEN(name))) [name]
FROM Users
ORDER BY user_id

This solution's runtime is 1656ms, making it the fastest among the four solutions, beating 68.25% of other submissions. Here's the performance snapshot:

Source Code 4

The final solution uses the STUFF function to replace the first character with its uppercase version, then lowers the entire string:

SELECT 
    user_id, 
    STUFF(LOWER(name), 1, 1, UPPER(LEFT(name, 1))) [name]
FROM Users
ORDER BY user_id

This solution's runtime is 1998ms, beating 28.86% of other submissions. Here's the performance snapshot:

Conclusion

All four solutions effectively solve the problem, albeit with different performance results on LeetCode. It's important to note that performance may vary in real-world RDMS due to various factors, including data volume, system hardware, SQL server configurations, etc.

Here's a ranking of these solutions based on LeetCode performance:

1. Source Code 3
2. Source Code 1
3. Source Code 4
4. Source Code 2

You can find the original problem at LeetCode.

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai