MSSQL] 구분자로 문자열 split하여 Table로 변환하기

구분자로 나눈 문자열을 param으로 Procedure에 전달했을때,

프로시져내부에서 해당 문자열을 잘라 사용해야할 경우가 있다. 

 

구분자로 나눈 문자열과 구분자를 parameter로 전달하면 index, value 컬럼의 테이블로 값을 반환하는 프로시져이다. 

CREATE     FUNCTION fn_J_Split(  
 @Expression VARCHAR(8000),  
 @Delimiter VARCHAR(10)  
)  
  
RETURNS @tblArray TABLE  
(  
 rIndex INT IDENTITY(1,1),  
 rValue VARCHAR(8000)  
)  
AS  
  
BEGIN  
 DECLARE @rValue VARCHAR(8000)  
  
 SET @Expression = replace(@Expression,', ',',')  
 SET @Expression = @Expression + @Delimiter  
  
  
 WHILE charindex(@Delimiter,@Expression) > 0  
 BEGIN  
  --// 하나씩 자르기..-_-;   
  SET @rValue = left(@Expression,charindex(@Delimiter,@Expression)-1)  
  
  
  --// 잘라낸 나머지로 다시 셋팅.  
  SET @Expression = substring(@Expression,charindex(@Delimiter,@Expression)+1,len(@Expression))  
  
  IF (@rValue <> '')  
     BEGIN  
   --// 테이블변수에 넣기  
   INSERT INTO @tblArray (  
    rValue  
   )   
   VALUES (  
    rTrim(@rValue)  
   )  
     END  
 END  
  
 RETURN  
END  

1. '홍길동,김길동,이길동'

SELECT * FROM [dbo].[fn_J_Split]('홍길동,김길동,이길동',',')

output : 

2. '홍길동|김길동|이길동'

SELECT * FROM [dbo].[fn_J_Split]('홍길동|김길동|이길동','|')

output : 

3. 'customer,01011112222,테스트고객|employee,01033334444,본사직원'

SELECT * INTO #params_smsSend FROM [dbo].[fn_J_Split]('customer,01011112222,테스트고객|employee,01033334444,본사직원','|')

DECLARE @i INT
DECLARE @total INT
DECLARE @table_receiver TABLE (
	Type VARCHAR(20),
	PhoneNum VARCHAR(20),
	Name VARCHAR(30)
)

SET @i = 1
SET @total = (SELECT COUNT(1) FROM #params_smsSend)

WHILE(@i <= @total)
BEGIN
	DECLARE @receiver VARCHAR(1000)
	SET @receiver = (SELECT rValue FROM #params_smsSend WHERE rIndex = @i)

	INSERT INTO @table_receiver (Type, PhoneNum, Name) VALUES
	(
		(SELECT rValue FROM [dbo].[fn_J_Split](@receiver,',') WHERE rIndex = 1),
		(SELECT rValue FROM [dbo].[fn_J_Split](@receiver,',') WHERE rIndex = 2),
		(SELECT rValue FROM [dbo].[fn_J_Split](@receiver,',') WHERE rIndex = 3)
	)

	SET @i = @i + 1
END

DROP TABLE #params_smsSend

select * from @table_receiver

output :