LeetCode MySQL-1667. Fix Names in a Table

    AllenLi
    LeetCode MySQL

    Write an SQL query 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.

    寫一個 SQL Query來修改名字，且只有第一個字母開頭為大寫，其餘為小寫。
回傳結果須進行 user_id 排序。

    Table: users

    +----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+
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.

    Example 1:

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

    Solution:
    1. 選擇 user_id , name的左邊第一個字元大寫，加上 name 的第二個字元之後皆為小寫。
    2. 來自 Users 的 table。
    3. 以 user_id進行排序。

    Code.1:

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

    Code.2:

    SELECT 
  user_id,
  CONCAT(UPPER(LEFT(name, 1)), LOWER(RIGHT(name, LENGTH(name) - 1))) AS 'name'
FROM Users
ORDER BY user_id;

//length.('Alice') = 5
//UPPER(left('Alice', 1)) = 'A'
//LOWER(RIGHT('Alice', 5 - 1)) = 'lice'

    newUsers

    Remark:
    CONCAT:串連兩個或兩個以上的字串。

    SELECT CONCAT ( 'Happy ', 'Birthday ', 11, '/', '25' ) AS Result;

//Happy Birthday 11/25

    SUBSTRING:取字串對應的位置。(ex.excel的 left、mid、right 函數

    SELECT x = SUBSTRING('abcdef', 2, 3);

//bcd

    Related Posts