Leetcode SQL 50 -- 1667. Fix Names in a Table

LeetCode > SQL_50 SQL

Created At : 2024-10-27 05:30

Count:218 Views :

Question
1. Table name: Users
Explaination
1. SQL SUBSTRING() Function
2. SQL UPPER(), LOWER(), and CONCAT()
Solution

Question

Table name: `Users`

Column Name	Type
user_id	int
name	varchar

This table contains the user_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.

Explaination

We can split the tasks into three part.

Split the name into first character and the remaining characters → SUBSTRING()
Modify them into upper case and lower case respectively → UPPER() and LOWER()
Order the result by user_id → ORDER BY

SQL SUBSTRING() Function

For extracting characters from a string, we can use the function SUBSTRING(<string>, start, end).
For example,

SUBSTRING(name, 1, 1)

returns the first character from string name,
and name without the first letter.

SUBSTRING(name, 2, LENGTH(name))

SQL UPPER(), LOWER(), and CONCAT()

For text converting, we can simply use UPPER(<text>) and LOWER(<text>).
Then we can combine two substring together by using

CONCAT(<string1>, <string2>, ... , <string_n>)

and finally, use ORDER BY user_id.

Solution

SELECT user_id, 
  CONCAT(
    UPPER(SUBSTRING(name, 1, 1)), 
    LOWER(SUBSTRING(name, 2, LENGTH(name)))
  ) AS name
FROM Users
ORDER BY user_id