[LeetCode][MySQL] 3465. Find Products with Valid Serial Numbers

Easy 🔗3465. Find Products with Valid Serial Numbers

📝문제 요약

Table: products

+--------------+------------+
| Column Name  | Type       |
+--------------+------------+
| product_id   | int        |
| product_name | varchar    |
| description  | varchar    |
+--------------+------------+
(product_id) is the unique key for this table.
Each row in the table represents a product with its unique ID, name, and description.

Write a solution to find all products whose description contains a valid serial number pattern. A valid serial number follows these rules:

• It starts with the letters SN (case-sensitive).
• Followed by exactly 4 digits.
• It must have a hyphen (-) followed by exactly 4 digits.
• The serial number must be within the description (it may not necessarily start at the beginning).

Return the result table ordered by product_id in ascending order.

The result format is in the following example.

Example:

Input:

products table:

+------------+--------------+------------------------------------------------------+
| product_id | product_name | description                                          |
+------------+--------------+------------------------------------------------------+
| 1          | Widget A     | This is a sample product with SN1234-5678            |
| 2          | Widget B     | A product with serial SN9876-1234 in the description |
| 3          | Widget C     | Product SN1234-56789 is available now                |
| 4          | Widget D     | No serial number here                                |
| 5          | Widget E     | Check out SN4321-8765 in this description            |
+------------+--------------+------------------------------------------------------+

Output:

+------------+--------------+------------------------------------------------------+
| product_id | product_name | description                                          |
+------------+--------------+------------------------------------------------------+
| 1          | Widget A     | This is a sample product with SN1234-5678            |
| 2          | Widget B     | A product with serial SN9876-1234 in the description |
| 5          | Widget E     | Check out SN4321-8765 in this description            |
+------------+--------------+------------------------------------------------------+

Explanation:

• Product 1: Valid serial number SN1234-5678
• Product 2: Valid serial number SN9876-1234
• Product 3: Invalid serial number SN1234-56789 (contains 5 digits after the hyphen)
• Product 4: No serial number in the description
• Product 5: Valid serial number SN4321-8765

The result table is ordered by product_id in ascending order.

✏️문제 풀이

SELECT

SELECT *

• 모든 컬럼(product_id, product_name, description)을 출력

FROM

FROM products

• products 테이블의 데이터를 가져옴

WHERE

WHERE description REGEXP '(^|[^A-Za-z0-9])SN[0-9]{4}-[0-9]{4}([^0-9]|$)'

• 정규식을 사용
  • (^|[^A-Za-z0-9]) : 문자열 시작 검사(시리얼 번호 앞에 아무것도 없거나, 특수문자나 공백이 있어야 함)
    • ^ : 문자열의 시작
    • [^A-Za-z0-9] : 알파벳(A-Z, a-z)이나 숫자(0-9)가 아닌 문자
  • SN : 정확히 대문자 S와 N이 연속되어 있어야 함 (소문자 안됨)
  • [0-9]{4} : SN 다음에는 정확히 숫자 4자리가 있어야 함
  • - : 하이픈 -이 반드시 있어야 함
  • [0-9]{4} : 하이픈 뒤에도 정확히 숫자 4자리가 있어야 함
  • ([^0-9]|$) : 문자열 끝 검사(시리얼 번호가 끝났을 때, 그 뒤에는 숫자가 오면 안됨)
    • [^0-9] : 숫자가 아닌 문자
    • $ : 문자열의 끝

ORDER BY

ORDER BY 1

• 1번 컬럼(product_id )를 기준으로 오름차순 정렬

💯제출 코드

SELECT *
FROM products
WHERE description REGEXP '(^|[^A-Za-z0-9])SN[0-9]{4}-[0-9]{4}([^0-9]|$)'
ORDER BY 1