mssql split 함수 3개

1. SPLIT 함수

/****** Object:  UserDefinedFunction [dbo].[FN_CDN_SPLIT]    Script Date: 2015-03-25 오전 11:21:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[FN_CDN_SPLIT]

(

     

     @LISTS VARCHAR(MAX)

   , @SPLIT VARCHAR(10) 

)

RETURNS @TB TABLE

(

POS INT IDENTITY PRIMARY KEY

  , VAL1 VARCHAR(30)

)

AS

BEGIN

 

   DECLARE

     @START SMALLINT

   , @END  SMALLINT

   , @CNT  SMALLINT

      , @SIZE SMALLINT

   , @SPLIT_SIZE SMALLINT

 SELECT @SPLIT_SIZE = LEN(@SPLIT)

 

 IF RIGHT(@LISTS, @SPLIT_SIZE) != @SPLIT

 BEGIN

  SET @LISTS = @LISTS + @SPLIT

 END

 

 SET @LISTS = @SPLIT + @LISTS

 SET @START = 1

 SELECT @END = CHARINDEX (@SPLIT, @LISTS, @START+@SPLIT_SIZE)

    SET @CNT = 0

 WHILE (1=1)

 BEGIN

  SET @START = CHARINDEX (@SPLIT, @LISTS)

  SELECT @END = CHARINDEX (@SPLIT, @LISTS, @START + @SPLIT_SIZE)

  IF @END <= 0 BREAK

  INSERT INTO @TB(VAL1) VALUES (SUBSTRING(@LISTS, @START+@SPLIT_SIZE, @END-@START-@SPLIT_SIZE))

  SELECT @LISTS = STUFF(@LISTS, @START, @SPLIT_SIZE, '')

     SET @CNT = @CNT + 1

 END

 RETURN

END

2. SPLIT_SET함수

CREATE FUNCTION [dbo].[FN_CDN_SPLIT_SET]

(

  @LISTS VARCHAR(MAX)

, @SPLIT VARCHAR(10)

, @GRP SMALLINT

)

RETURNS @TB TABLE

(

POS INT IDENTITY PRIMARY KEY

, VAL1 VARCHAR(100)

, VAL2 VARCHAR(100)

, VAL3 VARCHAR(100)

, VAL4 VARCHAR(100)

)

AS

BEGIN

DECLARE @RETURNSTR VARCHAR(100)

DECLARE @TEM_STR1 VARCHAR(100)

DECLARE @TEM_STR2 VARCHAR(100)

DECLARE @TEM_STR3 VARCHAR(100)

DECLARE @TEM_STR4 VARCHAR(100)

SET @TEM_STR1=''

SET @TEM_STR2=''

SET @TEM_STR3=''

SET @TEM_STR4=''

IF RIGHT(@LISTS, 1)!=@SPLIT

BEGIN

SET @LISTS =@LISTS+@SPLIT

END

DECLARE

@START SMALLINT

, @END SMALLINT

, @CNT SMALLINT

SET @START = 0

SET @END = -1

SET @CNT = 0

WHILE CHARINDEX (@SPLIT, @LISTS, @START+1) > 0

BEGIN

SET @END = CHARINDEX (@SPLIT, @LISTS, @START+1)

IF (@CNT % @GRP = 0)

BEGIN

SET @TEM_STR1=''

SET @TEM_STR2=''

SET @TEM_STR3=''

SET @TEM_STR4=''

END

IF (@CNT % @GRP= 0 ) 

BEGIN

SELECT @TEM_STR1 =SUBSTRING(@LISTS , @START+1, @END - @START -1) 

END

IF (@CNT % @GRP= 1 ) 

BEGIN

SELECT @TEM_STR2 =SUBSTRING(@LISTS , @START+1, @END - @START -1) 

END

IF (@CNT % @GRP= 2 ) 

BEGIN  

SELECT @TEM_STR3 =SUBSTRING(@LISTS , @START+1, @END - @START -1) 

END

IF (@CNT % @GRP= 3 ) 

BEGIN  

SELECT @TEM_STR4 =SUBSTRING(@LISTS , @START+1, @END - @START -1) 

END

if ((@CNT % @GRP) = @GRP-1)

BEGIN

INSERT INTO @TB(VAL1,VAL2, VAL3, VAL4) VALUES (@TEM_STR1, @TEM_STR2, @TEM_STR3, @TEM_STR4)

END

SET @START = CHARINDEX (@SPLIT, @LISTS, @END)

SET @CNT = @CNT + 1

END

RETURN

END