Function 테이블 타입 리턴

구분자로 나눠진 문자열을 잘라서 테이블 형태로 리턴 해주는 함수
#오라클
SPEC

TYPE TB_SPLIT IS TABLE OF VARCHAR2(32767);

 FUNCTION SPLIT
    (
        P_LIST VARCHAR2,
        P_DEL VARCHAR2
    ) RETURN TB_SPLIT PIPELINED;

BODY

FUNCTION SPLIT
    (
        P_LIST VARCHAR2,
        P_DEL VARCHAR2
    ) RETURN TB_SPLIT PIPELINED
    IS
        L_IDX  PLS_INTEGER;
        L_LIST VARCHAR2(32767) := P_LIST;
        L_VALUE VARCHAR2(32767);
    BEGIN

     LOOP

          L_IDX := INSTR(L_LIST,P_DEL);

           IF L_IDX > 0 THEN
                PIPE ROW(SUBSTR(L_LIST,1,L_IDX-1));
                L_LIST := SUBSTR(L_LIST,L_IDX+LENGTH(P_DEL));
            ELSE
                PIPE ROW(L_LIST);
                EXIT;
            END IF;

     END LOOP;
     RETURN;
END SPLIT;

사용 방법

SELECT
        *
FROM
        TB_USER T
                  INNER JOIN
        TABLE(SPLIT('a,b,c,d', ',') S
ON
        T.USER_ID = S.COLUMN_VALUE

#MSSQL

CREATE FUNCTION FN_SPLIT

(

    @in_value VARCHAR(1000),   

    @in_split VARCHAR(1)         

) 

RETURNS @out_value TABLE  ( VALUE VARCHAR(50) )

AS 

BEGIN   

    DECLARE @v_idx1 INT, @v_idx2 INT 

    DECLARE @v_temp VARCHAR(1000) 

    SET @v_idx1 = 1 

    SET @v_idx2 = 1 

    WHILE (@v_idx2 > 0)

    BEGIN 

        SET @v_idx2 = CHARINDEX(@in_split , @in_value , @v_idx1 ) 

        IF @v_idx2 = 0 

            SET @v_temp = RIGHT(@in_value , LEN(@in_value )-@v_idx1 +1 )

        ELSE

            SET @v_temp = SUBSTRING(@in_value , @v_idx1 , @v_idx2 -@v_idx1 )

        IF LEN(@v_temp )>0

            INSERT INTO @out_value VALUES( @v_temp )

        SET @v_idx1 = @v_idx2 +1 

    END 

   RETURN 

END