💡
Regex is hard. Luckily, we have chatGPT now.

List câu hỏi để làm quen với Regex và các hàm liên quan đến string/text trong SQL. Regex cực kỳ hữu dụng nhưng cũng rất khó hiểu, chỉ cần làm quen với những matching pattern đơn giản, những pattern nâng cao phức tạp cần thiết trong quá trình làm việc đều có thể sử dụng chatGPT với độ chính xác khá cao.

Câu 1667. Fix Names in a Table

Table: Users
+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| user_id        | int     |
| name           | varchar |
+----------------+---------+

Write a solution to fix the names so that only the first character is uppercase and the rest are lowercase.
Đáp án
SELECT
  user_id,
  UPPER(SUBSTRING(name FROM 1 FOR 1)) 
  || LOWER(SUBSTRING(name FROM 2)) AS name
from
  Users
order by
  user_id asc;

Câu 1527. Patients With a Condition

Table: Patients
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| patient_id   | int     |
| patient_name | varchar |
| conditions   | varchar |
+--------------+---------+

Write a solution to find the patient_id, patient_name, and conditions of the patients who have Type I Diabetes. Type I Diabetes always starts with DIAB1 prefix.
Đáp án
select
  patient_id,
  patient_name,
  conditions
from
  Patients
where
  conditions ~* '(^|[^a-zA-Z0-9+])\mDIAB1'
Giải thích

Test cases có một số trường hợp khá dị, đây là cách đơn giản nhất mình có thể tìm được để giải câu này.

Câu 196. Delete Duplicate Emails 

Table: Person
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+


Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.
Đáp án
delete from
  Person
where
  id not in (
    select
      id
    from
      (
        select
          min(id) as id,
          email
        from
          Person
        group by 2
      )
  );

Câu 176. Second Highest Salary 

Table: Employee
+-------------+------+
| Column Name | Type |
+-------------+------+
| id          | int  |
| salary      | int  |
+-------------+------+

Write a solution to find the second highest distinct salary from the Employee table. If there is no second highest salary, return null (return None in Pandas).S
Đáp án
select
  tmp.salary as SecondHighestSalary
from (values (2)) as t(rank)
left join (
    select
      salary,
      rank() over (order bysalary desc) as rank
    from
      (
        select salary
        from Employee
        group by 1 
        order by 1 desc
      )
  ) tmp on t.rank = tmp.rank

Câu 1484. Group Sold Products By The Date

Table Activities:
+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| sell_date   | date    |
| product     | varchar |
+-------------+---------+


Write a solution to find for each date the number of different products sold and their names.
Đáp án
select
  sell_date,
  count(distinct product) num_sold,
  STRING_AGG(distinct product, ',' order by product) products
from
  Activities
group by
  1

Câu 1327. List the Products Ordered in a Period

Table: Products
+------------------+---------+
| Column Name      | Type    |
+------------------+---------+
| product_id       | int     |
| product_name     | varchar |
| product_category | varchar |
+------------------+---------+

Table: Orders
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| product_id    | int     |
| order_date    | date    |
| unit          | int     |
+---------------+---------+

Write a solution to get the names of products that have at least 100 units ordered in February 2020 and their amount.
Đáp án
select
  p.product_name,
  sum(unit) unit
from
  Products p
  join Orders o on p.product_id = o.product_id
where
  date_trunc('month', o.order_date) = '2020-02-01' :: date
group by
  1
having sum(unit) >= 100
order by 2 desc

Câu 1517. Find Users With Valid E-Mails

Table: Users
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| user_id       | int     |
| name          | varchar |
| mail          | varchar |
+---------------+---------+
 
Write a solution to find the users who have valid emails
  - The prefix name is a string that may contain letters (upper or lower case), digits, underscore '_', period '.', and/or dash '-'. The prefix name must start with a letter.
  - The domain is '@leetcode.com'.
Đáp án
select
  *
from
  Users
where mail ~ '^[A-Za-z][A-Za-z0-9_.-]*@leetcode\.com$'