[SQL/오답] 정규표현식(REGEXP) 오탐 방지와 대소문자 구분 (LeetCode3465 Easy)

 

1. Problem

제품 설명(description) 내에서 특정 규칙(SN0000-0000)을 따르는 유효한 시리얼 번호를 찾아야 한다. 단순히 패턴을 포함하는지를 넘어, 시리얼 번호 뒤에 숫자가 더 붙는 경우(예: SN1234-56789)를 '오탐(False Positive)'으로 걸러내는 정밀한 필터링 설계가 요구된다.

2. Solution

단어 경계(\b)의 불안정성을 해결하기 위해 명시적 경계 조건을 설정하고, 캐릭터 셋 충돌 문제를 LIKE BINARY로 우회하여 해결한다.

• 정규식 패턴 분석: (^|[^A-Z0-9])와 ([^0-9A-Z]|$)를 사용하여 시리얼 번호 앞뒤에 다른 문자나 숫자가 붙어 있지 않음을 명확히 정의한다.
• 대소문자 구분 우회: REGEXP BINARY 사용 시 발생하는 캐릭터 셋 에러를 피하기 위해, LIKE BINARY를 별도로 사용하여 'SN' 대문자 포함 여부를 먼저 검증한다.

3. Takeaway (Trial & Error)

단순한 쿼리 작성보다 데이터의 무결성을 지키기 위한 '예외 케이스 방어' 과정에서 다음의 교훈을 얻는다.

• 단순 패턴 매칭의 함정: SN[0-9]{4}-[0-9]{4}만 사용하면 뒤에 숫자가 더 붙은 잘못된 번호까지 유효하다고 판단한다. '포함'이 아니라 '정확한 길이'를 제어하기 위한 경계 값 처리가 필수적이다.
• 환경에 따른 단어 경계(\b)의 가변성: DB 엔진마다 단어 경계를 인식하는 기준이 다를 수 있다. (^|[^A-Z0-9])처럼 무엇이 오면 안 되는지를 직접 명시하는 것이 가장 안전한 표준화 전략이다.
• 전처리 효율성: LIKE BINARY를 외부에 배치함으로써 인덱스 활용 가능성을 높이고, 대소문자를 먼저 거른 뒤 무거운 정규식을 실행하는 구조가 성능 면에서도 유리하다.

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

Find Products with Valid Serial Numbers - LeetCode

 

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.

1. 문제 개요

• 목표: 제품 설명(description)에서 유효한 시리얼 번호(SN0000-0000 형식) 추출
• 조건:
  1. SN으로 시작 (대소문자 구분 필수)
  2. 숫자 4자리 - 숫자 4자리 형식
  3. 시리얼 번호 뒤에 숫자가 더 붙으면 안 됨 (예: SN1234-56789는 오답)
  4. 문장 내 어디든 위치 가능

2. 정답 쿼리

SELECT product_id, product_name, description
FROM products
WHERE description REGEXP '(^|[^A-Z0-9])SN[0-9]{4}-[0-9]{4}([^0-9A-Z]|$)'
	AND description LIKE BINARY '%SN%'
ORDER BY product_id;

 

3. 주요 삽질기 (Trial & Error)

❌ 시도 1: 단순 패턴 매칭

SQL

 

WHERE description REGEXP 'SN[0-9]{4}-[0-9]{4}'

• 문제점: SN1234-56789처럼 뒤에 숫자가 더 붙은 경우도 앞부분만 인식해서 '유효'하다고 판단함 (오답 처리).

❌ 시도 2: 단어 경계(\b) 활용

SQL

 

WHERE description REGEXP '\\bSN[0-9]{4}-[0-9]{4}\\b'

• 문제점: DB 엔진에 따라 -나 숫자를 경계로 인식하는 기준이 달라 결과가 부정확함. 또한 MySQL 기본 설정에서 대소문자를 구분하지 못해 sn1234-5678까지 뽑히는 문제 발생.

❌ 시도 3: BINARY 키워드 사용

SQL

 

WHERE description REGEXP BINARY 'SN...'

• 문제점: Character set 'utf8mb3_general_ci' cannot be used in conjunction with 'binary' 에러 발생. 캐릭터 셋 충돌로 인해 실행 불가.