LEETCODE 1667- Fix Names in a Table - Solution in MySQL | LEETCODE 1667 Database SQL Solution

LEETCODE | 1667

Fix Names in a Table

LEETCODE 1667- Fix Names in a Table - Solution in MySQL | LEETCODE 1667 Database SQL Solution :

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.

 

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.

The query 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 of LEETCODE 1667 - Fix Names in a Table in MySQL:

Let's breakdown the steps that we are required to do in this before jumping into SQL

• FIrstly we need to fetch the first letter and convert it to upperCase

• Secondly we need to fetch the remaing string to lowerCase and then concat them

  We will be using four functions here that are present in SQL

• UPPER(A) where A is string
• LOWER(A) where A is string
• SUBSTR(A,index,length) where A is string index is starting index(1 index insead of 0 index) and length which is optional
  • So to get first letter we can use SUBSTR(name,1,1)
  • To get the remaining string we can use SUBSTR(name,2) // length is not required here
• CONCAT(A,B) where we concat two strings A+B

Now with above information we can create our query as following:

SELECT user_id,                                                          CONCAT(UPPER(SUBSTR(name,1,1)),                                         LOWER(SUBSTR(name,2,length(name)))                                 ) AS name                                            

FROM Users 

ORDER BY user_id;

Next Problem:

LEETCODE 1484 - Group Sold Products By The Date - Solution in MySQL | LEETCODE 1484 Database SQL Solution

Previous Problem: 

LEETCODE 196 - Delete Duplicate Emails - Solution in MySQL | LEETCODE 196 Database SQL Solution

Ashraf LEETCODE Solutions