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
Here's an example to better illustrate the problem:
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.
The first solution capitalizes the first character and changes the remaining characters to lowercase using the
SELECT user_id, UPPER(LEFT(name, 1)) + LOWER(RIGHT(name, LEN(name)-1)) [name] FROM Users ORDER BY user_id
The second solution uses the
CONCAT function in conjunction with the
SELECT user_id, CONCAT(UPPER(LEFT(name, 1)), LOWER(STUFF(name, 1, 1, ''))) [name] FROM Users ORDER BY user_id
The third solution changes the approach slightly, using the
SUBSTRING function instead of
SELECT user_id, UPPER(SUBSTRING(name, 1, 1)) + LOWER(SUBSTRING(name, 2, LEN(name))) [name] FROM Users ORDER BY user_id
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
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:
- Source Code 3
- Source Code 1
- Source Code 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.