--==============================================--将指定的字符串拆分多行数据--==============================================ALTER FUNCTION dbo.ufn_SplitString(  @SourceSql VARCHAR(MAX) ,  @StrSeprate VARCHAR(10))RETURNS @temp TABLE (C1 VARCHAR(MAX) )ASBEGIN    DECLARE @i INT    SET @SourceSql = RTRIM(LTRIM(@SourceSql))    SET @i = CHARINDEX(@StrSeprate, @SourceSql)    WHILE @i >= 1        BEGIN            INSERT  @temp            VALUES  ( LEFT(@SourceSql, @i - 1) )            SET @SourceSql = SUBSTRING(@SourceSql, @i + 1,                                       LEN(@SourceSql) - @i)            SET @i = CHARINDEX(@StrSeprate, @SourceSql)        END    IF @SourceSql <> '\'        INSERT  @temp        VALUES  ( @SourceSql )    RETURNENDGO--=====================================================--用法：SELECT * FROM dbo.ufn_SplitString('ABDC,BDF,DEF,,',',')--排除空字符串SELECT * FROM dbo.ufn_SplitString('ABDC,BDF,DEF,,',',')WHERE C1<>''

 版本2

--===============================================================--拆分脚本CREATE FUNCTION [dbo].[SplitString](      @Input NVARCHAR(MAX), --input string to be separated      @Separator NVARCHAR(MAX)=',', --a string that delimit the substrings in the input string      @RemoveEmptyEntries BIT=1 --the return value does not include array elements that contain an empty string)RETURNS @TABLE TABLE (      [Id] INT IDENTITY(1,1),      [VALUE] NVARCHAR(MAX)) ASBEGIN       DECLARE @Index INT, @Entry NVARCHAR(MAX)      SET @Index = CHARINDEX(@Separator,@Input)      WHILE (@Index>0)      BEGIN            SET @Entry=LTRIM(RTRIM(SUBSTRING(@Input, 1, @Index-1)))                      IF (@RemoveEmptyEntries=0) OR (@RemoveEmptyEntries=1 AND @Entry<>'')                    BEGIN                          INSERT INTO @TABLE([VALUE]) VALUES(@Entry)                    END                SET @Input = SUBSTRING(@Input, @Index+DATALENGTH(@Separator)/2, LEN(@Input))                SET @Index = CHARINDEX(@Separator, @Input)      END          SET @Entry=LTRIM(RTRIM(@Input))      IF (@RemoveEmptyEntries=0) OR (@RemoveEmptyEntries=1 AND @Entry<>'')            BEGIN                  INSERT INTO @TABLE([VALUE]) VALUES(@Entry)            END    RETURNEND --===============================================================--测试脚本DECLARE @str1 VARCHAR(MAX), @str2 VARCHAR(MAX), @str3 VARCHAR(MAX)SET @str1 = '1,2,3'SET @str2 = '1###2###3'SET @str3 = '1###2###3###'SELECT [VALUE] FROM [dbo].[SplitString](@str1, ',', 1)SELECT [VALUE] FROM [dbo].[SplitString](@str2, '###', 1)SELECT [VALUE] FROM [dbo].[SplitString](@str3, '###', 0)