SQL문제풀기

[SQL 문제 풀이] Find Products with Valid Serial Numbers (유효한 일련 번호를 가진 제품 찾기)

Jun 26, 2025

[SQL 문제 풀이] Find Products with Valid Serial Numbers (유효한 일련 번호를 가진 제품 찾기)

Contents

내가 작성한 쿼리

Find Products with Valid Serial Numbers - LeetCode

Can you solve this real interview question? 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.

Find Products with Valid Serial Numbers - LeetCode

https://leetcode.com/problems/find-products-with-valid-serial-numbers/description/

Find Products with Valid Serial Numbers - LeetCode

notion image


설명에 유효한 일련 번호 패턴이 포함된 모든 제품을 찾기 위한 솔루션을 작성합니다.
유효한 일련 번호는 다음 규칙을 따릅니다:

* SN(소문자 구분)으로 시작합니다.
* 그 뒤에 정확히 네 자리 숫자가 이어집니다.
* 하이픈(-) 뒤에 정확히 4자리 숫자가 있어야 합니다.
* 일련 번호는 설명 내에 있어야 합니다(반드시 처음부터 시작하는 것은 아닐 수도 있습니다).

product_id로 정렬된 결과 테이블을 오름차순으로 반환합니다.

내가 작성한 쿼리

MySQL


SELECT *
FROM products
WHERE description REGEXP '(^|[^0-9A-Za-z])SN\\d{4}-\\d{4}([^0-9]|$)';

Oracle


SELECT *
FROM products
WHERE REGEXP_LIKE(description, '(^|[^0-9A-Za-z])SN\d{4}-\d{4}([^0-9]|$)');

차이점

ㅤ	MySQL	Oracle
정규식 확인 함수 차이	`컬럼 REGEXP 정규식`	`REGEXP_LIKE('컬러명, 정규식)`
정규식 내부 차이	백슬래시 2개	1개

Share article

stupefyee

RSS·Powered by Inblog