배열에 대한 처리

/*****************************************************************************
  *  프로시저  명 : PB_GET_ARRAY_ITEM_FC
  *  단위  업무명 : 배열에 대한 처리
  *  입   력   값 : 배열처리문장,구분자,위치
  *  출   력   값 : 처리문장
  *  설        명 : 배열에 대한 처리
  *  작   성   자 : 
  *  작   성   일 : 2011-06-14
  *  수 정  사 항 : 홍길동– 일자
  *                 수정내용-
*****************************************************************************/
CREATE FUNCTION [dbo].[PB_GET_ARRAY_ITEM_FC](@ArrStr VARCHAR(8000), @DIV VARCHAR(12), @Loc INT)
RETURNS VARCHAR(8000) AS
BEGIN
       -- 구분자로구분지어진스트링을어레이처럼사용하게하는함수,
       -- 문자열자체에문제가있는것을잡아주지는않는다.
       -- Loc는1부터시작
       -- Loc이개수보다크면$ERROR$ 반환
 
       DECLARE @arr VARCHAR(8000)  -- 작업스트링변수
       DECLARE @Tmp VARCHAR(8000) -- 작업스트링변수
       DECLARE @RSLT VARCHAR(8000)      -- 결과값
       DECLARE @i                 INT          -- 임시위치변수
       DECLARE @UpArray    INT          -- 배열의크기
       DECLARE @CNT        INT          -- 임시카운터
       DECLARE @DIV_LEN    TINYINT     
 
       SET @DIV_LEN = LEN(@DIV)
 
       IF @ArrStr <> '' BEGIN
 
             SET @RSLT = '$ERROR$'
              SET @arr = @DIV + @ArrStr
 
             SET @Tmp = @ArrStr
             SET @UpArray = ((LEN(@Tmp) - LEN(REPLACE(@Tmp, @DIV, ''))) / @DIV_LEN ) + 1
 
             IF @Loc = -1 BEGIN
                    SET @RSLT = CAST(@UpArray AS VARCHAR(15))
                    RETURN @RSLT
             END
 
             IF (@Loc > @UpArray)OR (@Loc < 1) BEGIN
                    SET @RSLT = '$ERROR$'
                    RETURN @RSLT
             END
 
             IF (@Loc > 0) AND (@Loc <= @UpArray) BEGIN
                    SET @i = 0
                    SET @CNT = 0
 
                    WHILE @arr <> '' BEGIN
                           IF @CNT = @Loc BEGIN
                                 IF CHARINDEX(@DIV, @arr, @i) <> 0 BEGIN
                                         SET @RSLT = SUBSTRING(@arr, @i, CHARINDEX(@DIV, @arr, @i) - @i)
                                 END
                                 ELSE BEGIN
                                        SET @RSLT = SUBSTRING(@arr, @i, LEN(@arr) - (@i - 1))
                                 END
                                 BREAK
                           END         
                          
                           SET @i = (CHARINDEX(@DIV, @arr, @i) + @DIV_LEN)
                   
                           IF @i > 0 BEGIN                 
                                 SET @CNT = @CNT + 1       
                           END
                           ELSE BEGIN  
                                 BREAK
                           END
                    END
             END
       END
       ELSE BEGIN
             IF @Loc = -1
                    SET @RSLT = '0'
             ELSE
                    SET @RSLT = '@ERROR$'
       END
 
       RETURN @RSLT
 
END