Fix Names in a Table | LeetCode | MSSQL
Tags: leetcode, learning, programming, mssql
Published: 2023-07-04 17:05:00

## The Problem

Table: Users

| Column Name | Type    |
|-------------|---------|
| user_id     | int     |
| name        | varchar |

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:

| user_id | name  |
|---------|-------|
| 1       | aLice |
| 2       | bOB   |

### Output

| user_id | name  |
|---------|-------|
| 1       | Alice |
| 2       | Bob   |

## 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:

```sql
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:
![s1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2944u9f1fkykmwulx4fb.png)

### Source Code 2

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

```sql
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:
![s2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/y8g5ojbrmvbztmv3nbvd.png)

### Source Code 3

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

```sql
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:
![s3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vmbbu0g97n1sg7xb3jie.png)

### Source Code 4

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

```sql
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:
![s4](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/si0q4npf36xf30yslnlq.png)

## 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](https://leetcode.com/problems/fix-names-in-a-table/description/).

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}