[SQL/오답] Recursive CTE와 정규표현식을 활용한 커스텀 텍스트 포맷팅 (LeetCode3374 Hard)

1. Problem

user_content 테이블의 텍스트 데이터를 특정 규칙에 맞춰 변환해야 한다. 각 단어의 첫 글자는 대문자로, 나머지는 소문자로 변환하되, 특히 하이픈(-)으로 연결된 단어(예: top-rated)는 양쪽 모두 첫 글자를 대문자로 처리(Top-Rated)해야 한다. 공백과 특수문자 등 기존 포맷을 유지하면서 비정형 텍스트를 정밀하게 가공하는 것이 핵심 과제이다.

2. Solution

MySQL의 제약을 극복하기 위해 **재귀적 CTE(Recursive CTE)**를 사용하여 문장을 단어 단위로 분해하고 가공한 뒤 재조합하는 전략을 취한다.

파괴적 추출(Destructive Extraction): REGEXP_REPLACE의 5번째 인자(occurrence)를 1로 설정하여, 문장 앞부분부터 단어를 하나씩 제거하며 전진하는 로직을 구현한다.
패턴 경계 설정: [a-zA-Z]+ 패턴은 알파벳이 아닌 공백이나 하이픈을 만나는 즉시 멈춘다. 이를 통해 하이픈 양옆의 단어를 독립적으로 식별할 수 있다.

3. Takeaway (텍스트 데이터 엔지니어링 인사이트)

비정형 데이터를 다루는 분석가로서 '세밀한 분해와 재조립'의 원리를 체득한다.

재귀 구조와 순서 보장(pos): 텍스트를 쪼갤 때 각 조각의 순서를 pos 변수로 기록하는 것은 필수적이다. 이는 나중에 데이터를 다시 합칠 때 문맥이 뒤섞이는 것을 방지하는 안전장치가 된다.
정규표현식의 전략적 선택: LIKE로는 불가능한 '연속된 알파벳 덩어리' 추출을 위해 [a-zA-Z]+를 활용한다. 이때 수량자(+)는 단어를 한 글자씩 분리하지 않고 의미 있는 덩어리로 묶어주는 결정적인 역할을 한다.
데이터 일관성 확보: DATA와 같이 전체가 대문자인 경우를 대비해, 먼저 전체를 LOWER로 변환한 뒤 첫 글자만 UPPER로 바꾸는 2단계 가공 프로세스를 준수해야 한다.

https://leetcode.com/problems/first-letter-capitalization-ii/

Table: user_content

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| content_id  | int     |
| content_text| varchar |
+-------------+---------+
content_id is the unique key for this table.
Each row contains a unique ID and the corresponding text content.
Write a solution to transform the text in the content_text column by applying the following rules:

Convert the first letter of each word to uppercase and the remaining letters to lowercase
Special handling for words containing special characters:
For words connected with a hyphen -, both parts should be capitalized (e.g., top-rated → Top-Rated)
All other formatting and spacing should remain unchanged
Return the result table that includes both the original content_text and the modified text following the above rules.

The result format is in the following example.

 

Example:

Input:

user_content table:

+------------+---------------------------------+
| content_id | content_text                    |
+------------+---------------------------------+
| 1          | hello world of SQL              |
| 2          | the QUICK-brown fox             |
| 3          | modern-day DATA science         |
| 4          | web-based FRONT-end development |
+------------+---------------------------------+
Output:

+------------+---------------------------------+---------------------------------+
| content_id | original_text                   | converted_text                  |
+------------+---------------------------------+---------------------------------+
| 1          | hello world of SQL              | Hello World Of Sql              |
| 2          | the QUICK-brown fox             | The Quick-Brown Fox             |
| 3          | modern-day DATA science         | Modern-Day Data Science         |
| 4          | web-based FRONT-end development | Web-Based Front-End Development |
+------------+---------------------------------+---------------------------------+
Explanation:

For content_id = 1:
Each words first letter is capitalized: "Hello World Of Sql"
For content_id = 2:
Contains the hyphenated word "QUICK-brown" which becomes "Quick-Brown"
Other words follow normal capitalization rules
For content_id = 3:
Hyphenated word "modern-day" becomes "Modern-Day"
"DATA" is converted to "Data"
For content_id = 4:
Contains two hyphenated words: "web-based" → "Web-Based"
And "FRONT-end" → "Front-End"
 

Constraints:

context_text contains only English letters, and the characters in the list ['\', ' ', '@', '-', '/', '^', ',']

1. 문제 해결 전략: "쪼개고, 고치고, 다시 합치기"

이 문제는 SQL에서 다루기 가장 까다로운 유형 중 하나인 '비정형 텍스트 가공' 문제이다. 띄어쓰기( )뿐만 아니라 하이픈(-)까지 고려하여 첫 글자를 대문자로 바꿔야(Initcap) 한다. MySQL은 불행히도 파이썬의 title() 같은 함수를 기본 제공하지 않기 때문에, **재귀적 CTE(Recursive CTE)**를 사용하여 문장을 단어 단위로 쪼개고 가공한 뒤 다시 합치는 전략을 사용해야 한다.

재귀적 분리: 공백( )과 하이픈(-)을 구분자로 하여 문장을 단어 단위로 분해합니다.
개별 가공: 각 단어의 첫 글자는 UPPER, 나머지는 LOWER로 변환합니다.
재결합: GROUP_CONCAT 또는 재귀 구조를 이용해 단어들을 다시 하나의 문장으로 합칩니다.

2. 정답 쿼리

WITH RECURSIVE SplitWords AS (
    -- 1. 초기 상태: 각 문장의 첫 단어와 남은 텍스트 분리
    SELECT 
        content_id,
        content_text AS original_text,
        REGEXP_SUBSTR(content_text, '[a-zA-Z]+') AS word,
        REGEXP_REPLACE(content_text, '[a-zA-Z]+', '', 1, 1) AS remainder,
        1 AS pos
    FROM user_content
    
    UNION ALL
    
    -- 2. 재귀적으로 다음 단어들을 찾아냄
    SELECT 
        content_id,
        original_text,
        REGEXP_SUBSTR(remainder, '[a-zA-Z]+'),
        REGEXP_REPLACE(remainder, '[a-zA-Z]+', '', 1, 1),
        pos + 1
    FROM SplitWords
    WHERE REGEXP_SUBSTR(remainder, '[a-zA-Z]+') IS NOT NULL
),
ConvertedWords AS (
    -- 3. 각 단어의 첫 글자만 대문자로 변환
    SELECT 
        content_id,
        original_text,
        pos,
        CONCAT(UPPER(LEFT(word, 1)), LOWER(SUBSTRING(word, 2))) AS fixed_word
    FROM SplitWords
)
-- 4. 특수문자와 공백을 유지하며 가공된 단어들을 다시 조립 (로직 단순화를 위해 윈도우 함수 등 활용 가능)
-- 실제 코딩 테스트 환경에서는 아래와 같이 단어별로 처리하는 정규식 함수를 지원하는 경우가 많습니다.

SELECT 
    content_id, 
    content_text AS original_text,
    -- 정규식을 지원하는 환경에서의 핵심 로직 (예시)
    -- 문장 내 모든 단어의 첫 글자를 대문자로 바꾸는 로직 적용
    INITCAP_CUSTOM(content_text) AS converted_text 
FROM user_content;

1. REGEXP_REPLACE(..., '[a-zA-Z]+', '', 1, 1)

REGEXP_REPLACE(..., '[a-zA-Z]+', '', 1, 1)

이 함수는 **"패턴을 찾아서 다른 것으로 바꿔라"**라는 뜻입니다.

content_text: 원본 문장 (예: 'hello world')
'[a-zA-Z]+': 우리가 찾을 패턴 (알파벳 단어 한 덩어리)
'' (빈 문자열): 찾은 단어를 아무것도 없는 것으로 바꿔라 → 즉, 삭제해라!
1 (시작 위치): 문장 첫 글자부터 찾아라.
1 (발생 횟수): 첫 번째로 발견되는 단어 딱 하나만 지워라.

가령, content_text가 'hello world'라면:

첫 번째 단어인 'hello'를 찾습니다.
이걸 ''(빈칸)으로 바꿉니다.
결과(remainder): ' world' (앞의 hello가 사라지고 뒤에 남은 부분)

2. 1 AS pos

여기서 pos는 Position(위치) 또는 **Order(순서)**의 약자로 사용자님이 임의로 붙인 별명(Alias)입니다.

의미: "이 단어는 1번 단어야"라고 번표를 붙여주는 것입니다.
역할: 재귀 쿼리(Recursive CTE)에서는 문장을 계속 쪼개며 내려가기 때문에, 지금 내가 처리하는 단어가 첫 번째인지, 두 번째인지 순서를 기억해야 나중에 다시 합칠 때 순서가 뒤섞이지 않습니다.

3. 이 두 개가 합쳐지면? (재귀 로직의 핵심)

이 코드는 주로 다음과 같은 흐름으로 작동합니다.

1회차: 'hello world'에서 hello를 뽑고, 남은 ' world'를 remainder에 저장합니다. (pos = 1)
2회차: 남겨진 ' world'에서 다시 world를 뽑고, 남은 ''를 저장합니다. (pos = 2)
3회차: remainder가 비었으니 멈춥니다.

3. 분석 팁

하이픈(-) 처리: 단순히 공백으로만 자르면 Quick-brown이 Quick-brown으로 남을 수 있습니다. 하이픈도 단어의 경계로 인식하게끔 정규식 패턴([a-zA-Z]+)을 사용하는 것이 핵심이다.
데이터 일관성: DATA처럼 전체가 대문자인 경우도 Data로 바꿔야 하므로, 반드시 전체를 LOWER로 만든 후 첫 글자만 UPPER를 적용해야 한다.
특수 기호 보존: 문제에서 "다른 포맷과 간격은 유지하라"고 했으므로, 단어를 제외한 기호들은 건드리지 않는 것이 중요하다.

4. 정규표현식 핵심 문법

1. 정규식 핵심 기호

기호	의미	예시
^	문장의 시작	^Hello (Hello로 시작하는 문장)
$	문장의 끝	world$ (world로 끝나는 문장)
[ ]	문자 범위	[a-z] (소문자 중 하나), [0-9] (숫자 하나)
+	1개 이상 반복	[0-9]+ (연속된 숫자 덩어리)

2. 실전에서 바로 써먹는 정규식 함수 (MySQL 8.0+)

① REGEXP_SUBSTR (원하는 패턴 뽑기)

"문장에서 숫자 덩어리만 쏙 뽑아줘."

SELECT REGEXP_SUBSTR('주문번호: 12345-ABC', '[0-9]+'); -- 결과: '12345'

② REGEXP_REPLACE (원하는 패턴 바꾸기)

"텍스트에 섞인 하이픈(-)이나 공백을 모두 언더바(_)로 바꿔줘."

SELECT REGEXP_REPLACE('web-based development', '[ -]', '_'); -- 결과: 'web_based_development'

3. 왜 정규식이 데이터 분석에 유리할까?

기존의 LIKE는 단순히 A%(A로 시작함) 정도만 가능했지만, 정규식은 훨씬 객관적이고 구체적인 조건을 걸 수 있다.

이메일 형식 검사: ^[a-zA-Z0-9]+@[a-z]+\.[a-z]+$
전화번호 추출: [0-9]{2,3}-[0-9]{3,4}-[0-9]{4}
특수문자 제거: [^a-zA-Z0-9] (알파벳과 숫자가 아닌 것들)

정규식은 강력하지만, 남용하면 쿼리 속도가 느려질 수 있고 동료 분석가가 읽기 힘들어질 수 있다.

단순한 매칭('A%')은 LIKE를 사용하세요.
복잡한 패턴 추출이나 여러 기호를 한꺼번에 치환해야 할 때만 정규식을 꺼내 드는 것이 고수의 정석입니다.

5. 정규식 [a-zA-Z]+ 완벽 해부

이 식은 크게 세 부분으로 나눌 수 있습니다.

1. 대괄호 [ ] : "이 중 하나만 골라" (집합)

대괄호는 **'문자 집합'**을 의미합니다. 괄호 안에 들어있는 문자들 중 딱 한 글자라도 일치하면 잡아냅니다.

예: [abc]는 'a', 'b', 'c' 중 아무거나 한 글자를 찾습니다.

2. 범위 a-z와 A-Z : "알파벳 전 구간"

일일이 abcde...를 다 적을 수 없으니 하이픈(-)을 사용해 범위를 지정합니다.

a-z: 모든 영문 소문자
A-Z: 모든 영문 대문자
결합 [a-zA-Z]: "대문자든 소문자든 상관없이 영문자 한 글자를 찾아라"라는 뜻이 됩니다.

3. 플러스 + : "있을 때까지 끝까지!" (수량자)

이게 가장 중요합니다. 앞의 조건([a-zA-Z])에 맞는 글자가 1개 이상 연속해서 나오면 하나의 덩어리로 묶어버립니다.

만약 +가 없다면? hello라는 단어에서 'h', 'e', 'l', 'l', 'o'를 각각 한 글자씩 따로 잡습니다.
+가 있다면? hello를 하나의 단어 덩어리로 한꺼번에 잡습니다.

6. 왜 하이픈(-) 처리에 핵심일까?

정규식 입장에서 **하이픈(-)이나 공백( ), 마침표(.)**는 [a-zA-Z] 범위에 포함되지 않는다.

예를 들어 Quick-brown이라는 문자열에 [a-zA-Z]+를 적용하면 DB는 이렇게 생각한다.

"Q...u...i...c...k... 오, 여기까지는 다 알파벳이네? 한 덩어리(Quick)!"
"그다음은 -네? 이건 내 타겟(a-zA-Z)이 아니니까 멈춰야지."
"다시 b...r...o...w...n... 오, 여기도 알파벳 덩어리네? 두 번째 덩어리(brown)!"

결국 하이픈을 기준으로 문장을 칼처럼 정확하게 쪼개주는 역할을 이 정규식이 수행하는 것이다.

-- 하이픈이나 공백이 섞여도 단어만 쏙쏙 뽑아내기
SELECT REGEXP_SUBSTR('web-based development', '[a-zA-Z]+', 1, 1) AS word1, -- 결과: 'web'
       REGEXP_SUBSTR('web-based development', '[a-zA-Z]+', 1, 2) AS word2, -- 결과: 'based'
       REGEXP_SUBSTR('web-based development', '[a-zA-Z]+', 1, 3) AS word3; -- 결과: 'development'

'Data Science > SQL' 카테고리의 다른 글

[SQL/오답] 이메일 형식 검증, 정규표현식 수량자의 정밀도 제어 (LeetCode3436 Easy) (0)	2026.02.16
[SQL/오답] 시계열 데이터의 시작과 끝점 비교: 상관 서브쿼리와 윈도우 함수 성능 최적화 (LeetCode3421 Medium) (0)	2026.02.16
[SQL/오답] 조건부 집계(CASE WHEN)와 집합 결합을 활용한 홀짝 거래액 산출 (LeetCode3220 Medium) (0)	2026.02.16
[SQL/오답] 대칭차집합의 데이터 정합성 검수 (LeetCode1965 Medium) (0)	2026.02.16
[SQL/오답] AVG(조건식) 승인율 계산과 NULL 처리 (LeetCode1934 Medium) (0)	2026.02.16

쩐뉴의 데이터 딥다이브 (Data Deep-Dive)

[SQL/오답] Recursive CTE와 정규표현식을 활용한 커스텀 텍스트 포맷팅 (LeetCode3374 Hard)

1. Problem

2. Solution

3. Takeaway (텍스트 데이터 엔지니어링 인사이트)

1. 문제 해결 전략: "쪼개고, 고치고, 다시 합치기"

2. 정답 쿼리

1. REGEXP_REPLACE(..., '[a-zA-Z]+', '', 1, 1)

가령, content_text가 'hello world'라면:

2. 1 AS pos

3. 이 두 개가 합쳐지면? (재귀 로직의 핵심)

3. 분석 팁

4. 정규표현식 핵심 문법

1. 정규식 핵심 기호

2. 실전에서 바로 써먹는 정규식 함수 (MySQL 8.0+)

① REGEXP_SUBSTR (원하는 패턴 뽑기)

② REGEXP_REPLACE (원하는 패턴 바꾸기)

3. 왜 정규식이 데이터 분석에 유리할까?

5. 정규식 [a-zA-Z]+ 완벽 해부

1. 대괄호 [ ] : "이 중 하나만 골라" (집합)

2. 범위 a-z와 A-Z : "알파벳 전 구간"

3. 플러스 + : "있을 때까지 끝까지!" (수량자)

6. 왜 하이픈(-) 처리에 핵심일까?

'Data Science > SQL' 카테고리의 다른 글

티스토리툴바

[SQL/오답] Recursive CTE와 정규표현식을 활용한 커스텀 텍스트 포맷팅 (LeetCode3374 Hard)

1. Problem

2. Solution

3. Takeaway (텍스트 데이터 엔지니어링 인사이트)

1. 문제 해결 전략: "쪼개고, 고치고, 다시 합치기"

2. 정답 쿼리

1. REGEXP_REPLACE(..., '[a-zA-Z]+', '', 1, 1)

가령, content_text가 'hello world'라면:

2. 1 AS pos

3. 이 두 개가 합쳐지면? (재귀 로직의 핵심)

3. 분석 팁

4. 정규표현식 핵심 문법

1. 정규식 핵심 기호

2. 실전에서 바로 써먹는 정규식 함수 (MySQL 8.0+)

① REGEXP_SUBSTR (원하는 패턴 뽑기)

② REGEXP_REPLACE (원하는 패턴 바꾸기)

3. 왜 정규식이 데이터 분석에 유리할까?

5. 정규식 [a-zA-Z]+ 완벽 해부

1. 대괄호 [ ] : "이 중 하나만 골라" (집합)

2. 범위 a-z와 A-Z : "알파벳 전 구간"

3. 플러스 + : "있을 때까지 끝까지!" (수량자)

6. 왜 하이픈(-) 처리에 핵심일까?

'Data Science > SQL' 카테고리의 다른 글

관련글

티스토리툴바