구분값으로 구분된 키값을 테이블로 변환

*2016부터는 펑션을 만들어서 사용할 필요가 없음.

아래와 같이 하면 됨.

2016부터 추가된 함수

SELECT * FROM String_Split('56940840542,56940150020' ,',')

ALTER FUNCTION [dbo].[FN_Split] ( @Separator CHAR(1), @List VARCHAR(1000) ) RETURNS @RTN_TABLE TABLE(Item VARCHAR(30)) AS BEGIN DECLARE @IDX INT; DECLARE @vNewText VARCHAR(4000); IF @List = null RETURN; SET @IDX = CHARINDEX(@Separator, @List); WHILE NOT(@IDX = 0) BEGIN SET @vNewText = RTRIM(LTRIM(LEFT(@List, @IDX - 1))); SET @List = RIGHT(@List, LEN(@List) - @IDX); INSERT INTO @RTN_TABLE(Item) VALUES(@vNewText); SET @IDX = CHARINDEX(@Separator, @List); END INSERT INTO @RTN_TABLE(Item) VALUES(RTRIM(LTRIM(@List))); RETURN; END  

ALTER FUNCTION [dbo].[UF_FR_Split] ( @SplitData NVARCHAR(2000), @Delimeter NVARCHAR(5) ) RETURNS @RTNVALUE table ( ID INT IDENTITY(1,1), VAL NVARCHAR(100) ) AS BEGIN DECLARE @CNT INT SET @CNT = 1 WHILE (CHARINDEX(@Delimeter,@SplitData) > 0) BEGIN INSERT INTO @RTNVALUE (VAL) SELECT DATA = LTRIM(RTRIM(SUBSTRING(@SplitData,1,CHARINDEX(@Delimeter,@SplitData)-1))) SET @SplitData = SUBSTRING(@SplitData,CHARINDEX(@Delimeter,@SplitData)+1,LEN(@SplitData)) SET @CNT = @CNT + 1 END INSERT INTO @RTNVALUE (VAL) SELECT VAL = LTRIM(RTRIM(@SplitData)) RETURN END

SELECT * 

FROM [dbo].[FN_Split]( ',', '111,222,333,444,555,666,777,888,999,hh,hh,hhhh,hhhh,hhhh,hhhh,hhh,hhhh')