[SQLite] 쉼표로 구분된 문자열을 행으로 분할

하나의 컬럼에 "a,b,c"처럼 저장된 데이터를 여러 행으로 분할하는 방법

🎯 목표

SQLite에서 다음과 같은 데이터 변환을 수행하는 것

변환 전:

IDvalues_column1a,b,c2d,e3f,g,h,i

변환 후:

IDvalue1a1b1c2d2e3f3g3h3i

🛠️ 해결 방법들

방법 1: JSON 함수 사용 (SQLite 3.45+)

WITH split_data AS (
    SELECT 
        id,
        json_extract(value, '$') as split_value
    FROM your_table,
    json_each('["' || replace(values_column, ',', '","') || '"]')
)
SELECT id, trim(split_value) as value
FROM split_data
ORDER BY id;

장점: 간단하고 효율적

단점: 최신 SQLite 버전 필요, 특수문자 처리 주의

방법 2: 재귀 CTE 사용 (권장 ⭐)

WITH RECURSIVE split_string(id, remaining, extracted, pos) AS (
    -- 초기 단계: 끝에 쉼표를 추가하여 일관성 확보
    SELECT 
        id,
        values_column || ',' as remaining,
        '' as extracted,
        1 as pos
    FROM your_table
    WHERE values_column IS NOT NULL AND values_column != ''
    
    UNION ALL
    
    -- 재귀 단계: 첫 번째 값을 추출하고 나머지를 다음으로 전달
    SELECT 
        id,
        substr(remaining, instr(remaining, ',') + 1) as remaining,
        trim(substr(remaining, 1, instr(remaining, ',') - 1)) as extracted,
        pos + 1
    FROM split_string
    WHERE instr(remaining, ',') > 0 AND pos < 100
)
SELECT id, extracted as value
FROM split_string
WHERE extracted != '' AND extracted IS NOT NULL
ORDER BY id, pos;

장점:

모든 SQLite 버전에서 작동
특수문자 문제 없음
컬럼 개수 무제한

단점: 상대적으로 복잡

-- 테스트 데이터 생성
CREATE TABLE test_data (
    id INTEGER PRIMARY KEY,
    values_column TEXT
);

INSERT INTO test_data (values_column) VALUES 
('a,b,c'),
('d,e'),
('f,g,h,i'),
('j');

-- 방법 1 사용 (가장 추천)
WITH split_data AS (
    SELECT 
        id,
        json_extract(value, '$') as split_value
    FROM test_data,
    json_each('["' || replace(values_column, ',', '","') || '"]')
)
SELECT id, trim(split_value) as value
FROM split_data
ORDER BY id;

🔍 재귀 CTE 작동 원리 분석

핵심 아이디어

끝에 쉼표 추가: "a,b,c" → "a,b,c," 로 변환하여 일관된 처리
점진적 추출: 매번 첫 번째 값만 추출하고 나머지는 다음 단계로
자동 종료: 더 이상 쉼표가 없으면 재귀 중단

실행 과정 시뮬레이션

입력: values_column = "apple,banana,cherry"

단계remainingextractedpos초기"apple,banana,cherry,"""11차"banana,cherry,""apple"22차"cherry,""banana"33차"""cherry"4종료(쉼표 없음)--

핵심 함수들

-- 첫 번째 쉼표 위치 찾기
instr(remaining, ',')

-- 첫 번째 쉼표 이전 부분 (현재 값)
substr(remaining, 1, instr(remaining, ',') - 1)

-- 첫 번째 쉼표 이후 부분 (남은 문자열)
substr(remaining, instr(remaining, ',') + 1)

🚨 JSON 방법의 함정과 해결책

JSON 함수를 사용할 때 다음 오류가 발생할 수 있습니다:

[SQLITE_ERROR] SQL error or missing database (malformed JSON)

원인

쌍따옴표 (") 포함
백슬래시 (\\) 포함
개행문자, 탭 문자 포함

해결책

-- 특수문자 이스케이핑
WITH safe_json AS (
    SELECT 
        id,
        '["' || replace(
            replace(
                replace(values_column, '\', '\\'), 
                '"', '\"'
            ), 
            ',', '","'
        ) || '"]' as json_array
    FROM your_table
    WHERE json_valid('["' || replace(values_column, ',', '","') || '"]') = 1
)
-- ... 이후 처리

💡 실전 응용 사례

학생 수업 프로그램 데이터 분할

실제 업무에서 자주 마주치는 케이스입니다:

WITH RECURSIVE 
-- 데이터 정리
cleaned_data AS (
    SELECT
        `등록일`,
        `이름`,
        `학생연락처`,
        `pageID`,
        REPLACE(`수업프로그램`, '페이지 아이디: ', '') AS `cleaned_programs`
    FROM 재원학생
    WHERE `수업프로그램` IS NOT NULL AND `수업프로그램` != ''
),
-- 재귀로 프로그램 분할
split_programs(등록일, 이름, 학생연락처, pageID, remaining, extracted, pos) AS (
    SELECT 
        `등록일`, `이름`, `학생연락처`, `pageID`,
        `cleaned_programs` || ',' as remaining,
        '' as extracted,
        1 as pos
    FROM cleaned_data
    
    UNION ALL
    
    SELECT 
        등록일, 이름, 학생연락처, pageID,
        substr(remaining, instr(remaining, ',') + 1) as remaining,
        trim(substr(remaining, 1, instr(remaining, ',') - 1)) as extracted,
        pos + 1
    FROM split_programs
    WHERE instr(remaining, ',') > 0 AND pos < 100
)
SELECT
    ROW_NUMBER() OVER (ORDER BY `pageID`, extracted) AS `record_id`,
    `등록일`, `이름`, `학생연락처`,
    extracted AS `수업프로그램_ID`,
    `pageID`
FROM split_programs
WHERE extracted != '' AND extracted IS NOT NULL
ORDER BY `record_id`;

⚡ 성능 최적화 팁

1. 인덱스 활용

-- 분할 후 임시 테이블에 인덱스 생성
CREATE INDEX idx_split_result ON temp_split_table(original_id, split_value);

2. 배치 처리

대용량 데이터의 경우:

-- ID 범위별로 나누어 처리
WHERE id BETWEEN 1 AND 10000

3. 재귀 깊이 제한 조정

-- 더 많은 분할이 필요한 경우
WHERE instr(remaining, ',') > 0 AND pos < 1000

🔄 다른 구분자 응용

세미콜론으로 분할

-- 쉼표 대신 세미콜론
values_column || ';' as remaining
substr(remaining, instr(remaining, ';') + 1)

여러 구분자 동시 처리

-- 쉼표, 세미콜론, 파이프 모두 쉼표로 통일
replace(replace(replace(values_column, ';', ','), '|', ','), ':', ',') || ','

📋 방법별 비교표

방법호환성성능복잡도특수문자 처리JSON 함수SQLite 3.45+⭐⭐⭐⭐⚠️ 주의 필요재귀 CTE모든 버전⭐⭐⭐⭐✅ 안전고정 분할모든 버전⭐⭐⭐⭐⭐⭐✅ 안전

🎯 결론 및 권장사항

일반적인 경우: 재귀 CTE 방법 사용 (가장 안전하고 유연)
최신 SQLite + 특수문자 없음: JSON 함수 방법
고정된 분할 개수: 직접 분할 방법

재귀 CTE 방법은 약간 복잡해 보이지만, 한 번 이해하면 어떤 상황에서든 안정적으로 작동하는 강력한 도구입니다!