Oracle 문자열 분리 Split: 방법과 활용 예시

Oracle에서 데이터를 다룰 때 문자열을 특정 구분자로 분리해야 하는 경우가 자주 발생합니다. 특히 쉼표(,), 세미콜론(;), 또는 공백과 같은 구분자를 기준으로 데이터를 나누어 처리하는 것은 데이터 정제와 분석의 필수 과정이죠. 이 글에서는 Oracle에서 문자열을 Split하는 다양한 방법과 함께 실용적인 예제를 공유합니다.

---

1. REGEXP_SUBSTR를 이용한 문자열 Split

Oracle 10g부터 제공되는 REGEXP_SUBSTR 함수는 정규 표현식을 사용하여 특정 구분자로 나뉜 문자열을 추출하는 데 유용합니다.

예제: 쉼표로 구분된 문자열 분리

SELECT 
    REGEXP_SUBSTR('apple,banana,grape', '[^,]+', 1, 1) AS part1,
    REGEXP_SUBSTR('apple,banana,grape', '[^,]+', 1, 2) AS part2,
    REGEXP_SUBSTR('apple,banana,grape', '[^,]+', 1, 3) AS part3
FROM DUAL;

결과:

apple

banana

grape

Tip:

• [^,]+는 쉼표를 제외한 문자를 추출합니다.
• 마지막 매개변수는 추출하려는 항목의 순서를 나타냅니다.

---

2. 문자열을 행으로 Split하기

Oracle에서는 계층형 쿼리를 활용하여 하나의 문자열을 여러 행으로 나눌 수 있습니다.

예제: 쉼표로 구분된 문자열을 행으로 변환

WITH DATA AS (
    SELECT 'apple,banana,grape' AS str FROM DUAL
)
SELECT 
    REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL) AS split_value
FROM DATA
CONNECT BY LEVEL <= LENGTH(str) - LENGTH(REPLACE(str, ',', '')) + 1;

결과:

apple

banana

grape

설명:

• LENGTH(str) - LENGTH(REPLACE(str, ',', ''))는 문자열에 포함된 쉼표의 개수를 계산합니다.
• CONNECT BY LEVEL을 통해 각 항목을 반복적으로 추출합니다.

---

3. INSTR과 SUBSTR을 사용한 Split

INSTR과 SUBSTR을 조합하여 문자열의 특정 구간을 추출하는 방법도 있습니다. 이 방식은 Oracle 9i 이하 버전에서도 사용할 수 있어 유용합니다.

예제 1: 첫 번째 값 추출

SELECT 
    SUBSTR('apple,banana,grape', 1, INSTR('apple,banana,grape', ',') - 1) AS first_part
FROM DUAL;

결과:

apple

예제 2: 나머지 값 추출

SELECT 
    SUBSTR('apple,banana,grape', INSTR('apple,banana,grape', ',') + 1) AS remaining_part
FROM DUAL;

결과:

banana,grape

---

4. 사용자 정의 함수로 Split 구현

복잡한 Split 작업이 반복적으로 필요하다면 PL/SQL을 사용하여 사용자 정의 함수를 만드는 것이 효과적입니다.

PL/SQL 사용자 정의 함수 예제

CREATE OR REPLACE FUNCTION SPLIT_STRING(p_string IN VARCHAR2, p_delimiter IN VARCHAR2, p_part IN NUMBER)
RETURN VARCHAR2 IS
    v_result VARCHAR2(4000);
BEGIN
    SELECT REGEXP_SUBSTR(p_string, '[^' || p_delimiter || ']+', 1, p_part)
    INTO v_result
    FROM DUAL;
    RETURN v_result;
END;
/

사용 예제

SELECT SPLIT_STRING('apple,banana,grape', ',', 2) AS second_part FROM DUAL;

결과:

banana

장점:

• 코드 재사용성이 높아집니다.
• 복잡한 문자열 처리를 간단하게 해결할 수 있습니다.

---

5. XMLTABLE로 문자열 Split하기

XMLTABLE을 활용하면 문자열을 XML 구조로 변환하여 행 단위로 처리할 수 있습니다.

예제: XMLTABLE을 활용한 행 변환

SELECT *
FROM XMLTABLE(
    ('"' || REPLACE('apple,banana,grape', ',', '","') || '"')
    COLUMNS value VARCHAR2(100) PATH '.'
);

결과:

apple

banana

grape

---

6. 어떤 Split 방법을 선택할까?

• 간단한 문자열 추출: REGEXP_SUBSTR를 사용하세요.
• 다수의 결과를 행으로 변환: CONNECT BY LEVEL 또는 XMLTABLE이 적합합니다.
• 반복적 사용: PL/SQL 사용자 정의 함수가 효율적입니다.
• 구 버전 호환성: INSTR과 SUBSTR을 활용하세요.

---

결론

Oracle에서 문자열 Split은 데이터 처리의 기본이면서도 중요한 기술입니다. 프로젝트 상황에 맞는 적절한 방법을 선택하여 작업의 효율성을 높여보세요.