{x}
blog image

Fix Names in a Table

Table: Users

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
user_id is the primary key (column with unique values) for this table.
This table contains the ID and the name of the user. The name consists of only lowercase and uppercase characters.

 

Write a solution to fix the names so that only the first character is uppercase and the rest are lowercase.

Return the result table ordered by user_id.

The result format is in the following example.

 

Example 1:

Input: 
Users table:
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | aLice |
| 2       | bOB   |
+---------+-------+
Output: 
+---------+-------+
| user_id | name  |
+---------+-------+
| 1       | Alice |
| 2       | Bob   |
+---------+-------+

Solution Explanation for Fixing Names in a Table

This problem requires modifying names in a database table so that the first letter is uppercase and the rest are lowercase. We can achieve this using SQL string functions. Both solutions presented use CONCAT to combine the modified parts of the name.

Solution 1: Using LEFT and SUBSTRING

This solution uses the following SQL functions:

  • LEFT(name, 1): Extracts the first character of the name column.
  • UPPER(...): Converts the extracted character to uppercase.
  • SUBSTRING(name, 2): Extracts the substring starting from the second character to the end of the name.
  • LOWER(...): Converts the extracted substring to lowercase.
  • CONCAT(...): Combines the uppercase first character and the lowercase rest of the name.
  • ORDER BY user_id: Sorts the results by user_id.

MySQL Code:

SELECT
    user_id,
    CONCAT(UPPER(LEFT(name, 1)), LOWER(SUBSTRING(name, 2))) AS name
FROM
    users
ORDER BY
    user_id;

Solution 2: Using DATALENGTH for Explicit Length

This solution is very similar to the first one, but it explicitly defines the length of the substring using DATALENGTH(name). This makes the code slightly more robust but functionally equivalent to Solution 1 in most cases. DATALENGTH gets the length of the string in bytes.

MySQL Code:

SELECT
    user_id,
    CONCAT(
        UPPER(LEFT(name, 1)),
        LOWER(SUBSTRING(name, 2, DATALENGTH(name)))
    ) AS name
FROM
    users
ORDER BY
    user_id;

Time and Space Complexity Analysis

Both solutions have the same time and space complexity:

  • Time Complexity: O(N), where N is the number of rows in the Users table. This is because the SQL query processes each row individually to modify the name.
  • Space Complexity: O(1). The space used is constant regardless of the number of rows, as the query doesn't store intermediate results proportionally to the input size. The output size is the same as the input size.

Other Database Systems

The core logic using string functions (UPPER, LOWER, SUBSTRING, LEFT, CONCAT) can be adapted to other database systems (PostgreSQL, SQL Server, Oracle, etc.) with minor syntactic changes. The specific functions might have slightly different names (e.g., SUBSTR instead of SUBSTRING), but the overall approach remains the same. For instance, in PostgreSQL, LEFT might be substring(name from 1 for 1) and SUBSTRING is the same, DATALENGTH could be length.