본문 바로가기
프로그래밍/DBMS

MSSQL 문자열 잘라서 테이블 반환 함수

by zoo10 2013. 4. 24.









ALTER FUNCTION [dbo].[FN_SPLIT]

(

    @StrValue VARCHAR(MAX),   -- 분리할 문자열

    @SplitChar VARCHAR(1)         -- 구분할 문자

RETURNS @SPLIT_TEMP TABLE  ( VALUE VARCHAR(50) )

AS 

BEGIN   

    DECLARE @oPos INT, @nPos INT

    DECLARE @TmpVar VARCHAR(1000) -- 분리된 문자열 임시 저장변수


    SET @oPos = 1 -- 구분문자 검색을 시작할 위치

    SET @nPos = 1 -- 구분문자 위치


    WHILE (@nPos > 0)

    BEGIN

        SET @nPos = CHARINDEX(@SplitChar, @StrValue, @oPos )


        IF @nPos = 0 

            SET @TmpVar = RIGHT(@StrValue, LEN(@StrValue)-@oPos+1 )

        ELSE

            SET @TmpVar = SUBSTRING(@StrValue, @oPos, @nPos-@oPos)


        IF LEN(@TmpVar)>0

            INSERT INTO @SPLIT_TEMP VALUES( @TmpVar )

        SET @oPos = @nPos +1 

    END

   RETURN 

END


위 함수의 사용법 


select * from (
select a.*, b.col1 from table1 a, table2 b
where a.tempcol = b.tempcol
) a
inner join dbo.FN_SPLIT('101,102', ',') b on a.col1 = b.VALUE
inner join dbo.FN_SPLIT('1,2',',') c on a.col2 = c.VALUE
inner join dbo.FN_SPLIT('A,B', ',') d on a.col3 like '' + d.VALUE + '%'


또는 


create Function [dbo].FN_ParamToList
(
    @List NVarChar(Max)
)
Returns @IDList TABLE 
(
    ID varchar(50) 
)
With SchemaBinding
As
Begin
    Declare @myList NVarChar(Max);
    Declare @value NvarChar(Max);

    SET @myList = @List;

    While(Charindex(',', @myList) != 0)
    Begin
        SET @value = Substring(@myList , 1, Charindex(',', @myList) - 1);
        Insert Into @IDList (ID) Values (@value)
        SET @myList = Substring(@myList, Charindex(',', @myList) + 1, Len(@myList));
    End

    Insert Into @IDList (ID) Values (@myList)

    Return;
End


함수 등록하고 사용법은 아래와 같이 하면 된다.

select * from (
select a.*, b.tempcol from table1 a, table2 b
where a.col1 = b.col1

) a
inner join dbo.FN_ParamToList('101,102') b on a.col1= b.id
inner join dbo.FN_ParamToList('1,2') c on a.col2 = c.id
inner join dbo.FN_ParamToList('A,B') d on a.col3 like '' + d.id + '%'