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;


첫번째 매개변수 : 자를 문자열

두번째 매개변수 : 구분자

세번째 매개변수 : 추출해낼 위치


to Top