오라클 SPLIT 구분자로 자르기 함수 구현
CREATE OR REPLACE FUNCTION FUNC_SPLIT(COLNAME VARCHAR, SEP VARCHAR, NUM NUMBER)
RETURN VARCHAR
IS
STR VARCHAR2(512);
BEGIN
IF NUM > 0 THEN
IF INSTR(COLNAME, SEP, 1, NUM+1) < 1 THEN
IF INSTR(COLNAME, SEP, 1, NUM) > 0 THEN
SELECT SUBSTR(COLNAME, INSTR(COLNAME, SEP, 1, NUM) + LENGTH(SEP), LENGTH(COLNAME)) INTO STR FROM DUAL;
END IF;
END IF;
IF INSTR(COLNAME, SEP, 1, NUM+1) > 0 THEN
SELECT SUBSTR(COLNAME, INSTR(COLNAME, SEP, 1, NUM) + LENGTH(SEP), (INSTR(COLNAME, SEP, 1, NUM+1) - INSTR(COLNAME, SEP, 1, NUM) - LENGTH(SEP))) INTO STR FROM DUAL;
END IF;
END IF;
IF NUM = 0 THEN
SELECT SUBSTR(COLNAME, 0, INSTR(COLNAME, SEP, 1, 1) - 1) INTO STR FROM DUAL;
END IF;
RETURN STR;
END func_split;
사용법
예) SELECT FUNC_SPLIT('a$b$c', '$', 0) FROM DUAL;
첫번째 매개변수 : 자를 문자열
두번째 매개변수 : 구분자
세번째 매개변수 : 추출해낼 위치