SQL字符串分隔函数(fn_split)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_split]
(
@Source VARCHAR(MAX), ---The string to be separated
@Separator CHAR(1)  　　---Separator
)
RETURNS @TB TABLE(VL VARCHAR(100))
AS
BEGIN
 DECLARE @Tmp VARCHAR(MAX)
 DECLARE @VL VARCHAR(100)
 SET @Tmp = @Source
 WHILE @Tmp <> ''
  BEGIN
   IF CHARINDEX(@Separator,@Tmp) = 0
    BEGIN
     SET @VL = LTRIM(RTRIM(@Tmp))
     IF @VL <> ''
      BEGIN
       INSERT INTO @TB SELECT @VL
       SET @Tmp = ''
      END
    END
   ELSE
    BEGIN
     SET @VL = SUBSTRING(@Tmp,0,CHARINDEX(@Separator,@Tmp))
     SET @VL = LTRIM(RTRIM(@VL))
     IF @VL <> ''
      BEGIN
       INSERT INTO @TB SELECT @VL
      END
     SET @Tmp = SUBSTRING(@Tmp,CHARINDEX(@Separator,@Tmp) + 1,LEN(@Tmp))
     SET @Tmp = LTRIM(RTRIM(@Tmp))     
    END
  END
 RETURN;
END

调用示例：

declare @source varchar(max)
declare @separator char(1)
set @source = '10,12,1,3,14,15,17,18,,,,19,,'
set @separator = ','

select * from dbo.fn_split(@source,@separator)