USE [CORE_SYSTEM]

GO

/****** Object:  UserDefinedFunction [dbo].[fn_Split]    Script Date: 2012/12/14 上午 10:32:45 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

 

ALTER  FUNCTION [dbo].[fn_Split](@text nvarchar(MAX), @delimiter nvarchar(1) = N' ')

RETURNS @Strings TABLE

(   

  position int IDENTITY PRIMARY KEY,

  value Nvarchar(MAX)  

)

AS

BEGIN

DECLARE @index int

SET @index = -1

WHILE (LEN(@text) > 0)

  BEGIN 

    SET @index = CHARINDEX(@delimiter, @text) 

    IF (@index = 0) AND (LEN(@text) >= 0) 

      BEGIN  

        INSERT INTO @Strings (VALUE) VALUES (@text)

          BREAK 

      END 

    IF (@index >= 1) 

      BEGIN  

        INSERT INTO @Strings (VALUE) VALUES (--LEFT(@text, @index - 1))

CASE @index WHEN 1 THEN '' ELSE LEFT(@text, @index - 1) END)  

        SET @text = RIGHT(@text, (LEN(@text) - @index)) 

      END 

    ELSE

      SET @text = RIGHT(@text, (LEN(@text) - @index))

    END

  RETURN

END