[MSSQL] 문자열 구분자로 쪼개기/나누기 (split)

문자열: 'AB^CDE^FGH^^IJK'

SELECT [dbo].[FN_SPLIT]('AB^CDE^FGH^^IJK', '^', 1);

결과: AB

SELECT [dbo].[FN_SPLIT]('AB^CDE^FGH^^IJK', '^', 2);

결과: CDE

SELECT [dbo].[FN_SPLIT]('AB^CDE^FGH^^IJK', '^', 3);

결과: FGH

SELECT [dbo].[FN_SPLIT]('AB^CDE^FGH^^IJK', '^', 4);

결과: 공백

SELECT [dbo].[FN_SPLIT]('AB^CDE^FGH^^IJK', '^', 5);

결과: IJK

-- 함수 FN_SPLIT 생성

CREATE FUNCTION [dbo].[FN_SPLIT]

(

 @STRING VARCHAR(MAX )     -- 문자열

,@DELI VARCHAR (10)            -- 구분자

,@POSITION INT                   -- 위치

)

RETURNS VARCHAR (200)

AS

BEGIN

 DECLARE

  @START SMALLINT

 ,@END SMALLINT

 ,@COUNTER SMALLINT

 ,@DELISIZE SMALLINT

 ,@RET VARCHAR(200 )

 SELECT @DELISIZE = LEN (@DELI)

 

 IF RIGHT(@STRING, @DELISIZE )!=@DELI

 BEGIN

  SET @STRING =@STRING+ @DELI

 END

 SET @STRING =@DELI+ @STRING

 

 SET @START = 1

 SELECT @END = CHARINDEX (@DELI ,@STRING , @START+@DELISIZE )

 SET @COUNTER = 0

 WHILE ( 1=1 )

 BEGIN

  SET @START = CHARINDEX (@DELI ,@STRING )

  SELECT @END = CHARINDEX (@DELI ,@STRING , @START+@DELISIZE )

        IF @END <= 0 BREAK

  SET @RET = SUBSTRING(@STRING ,@START+ @DELISIZE ,@END-@START -@DELISIZE)

  SELECT @STRING = STUFF (@STRING, @START,@DELISIZE ,'')

  SET @COUNTER = @COUNTER + 1

  IF( @COUNTER = @POSITION )

        BREAK;

 END

 RETURN @RET

END