/*==================================================================================================

P_ARRAY               VARCHAR2,     예) 테스트치⊥테스트⊥테스⊥

P_CASE                VARCHAR2,      예) ⊥ 
P_ORDER               NUMBER         예) 1

위의 예로 실행을 했을때 단어 끝에 '치' 라는 글자만 들어가면 깨집니다.

어떻게 처리를 해야하는지 답변주시면 감사하겠습니다.

AMERICAN_AMERICA.US7ASCII 입니다.

==================================================================================================*/

CREATE OR REPLACE FUNCTION F_ARRAY
(
    P_ARRAY               VARCHAR2,                                                                 --배열 문자
    P_CASE                VARCHAR2,                                                                 --구분자
    P_ORDER               NUMBER                                                                    --순서
)
RETURN VARCHAR2
IS
    V_I                   PLS_INTEGER := 0;
    V_J                   PLS_INTEGER := 0;
    V_S                   NUMBER;
BEGIN
    IF P_ARRAY IS NULL THEN RETURN P_ARRAY; END IF;
    IF P_ORDER = 0 THEN RETURN NULL; END IF;
   
    V_S := LENGTH(P_CASE);
   
    IF P_ORDER = 1 THEN

        V_I := INSTR(P_ARRAY, P_CASE);

        IF V_I > 0 THEN RETURN RTRIM(SUBSTR(P_ARRAY, 1, V_I - 1), CHR(161));
        ELSE            RETURN P_ARRAY;
        END IF;

    END IF;

    V_I := INSTR(P_ARRAY, P_CASE, 1, P_ORDER - 1);
    V_J := INSTR(P_ARRAY, P_CASE, 1, P_ORDER);

    IF V_I = 0 AND V_J = 0 THEN RETURN NULL; END IF;
       
    IF V_I <> 0 AND V_J = 0 THEN RETURN RTRIM(SUBSTR(P_ARRAY, V_I + V_S), CHR(161)); END IF;
   
    RETURN RTRIM(SUBSTR(P_ARRAY, V_I + V_S, V_J - V_I - 1), CHR(161));

EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;