函数

CREATE FUNCTION Split(    @Str VARCHAR(MAX),    @Separator VARCHAR(10))RETURNS @Res TABLE( Strs VARCHAR(MAX))AS BEGIN     DECLARE @Index INT    DECLARE @StartPoint INT    DECLARE @LEN INT                                                                                                                       DECLARE @Flag BIT    SET @Flag = 1    SET @Index = 0    WHILE @Flag <> 0    BEGIN        SET @StartPoint = @Index + 1        SET @Index = CHARINDEX(@Separator,@Str,@StartPoint)        SET @LEN = @Index - @StartPoint        IF @Index = 0         BEGIN            SET @Flag = 0            SET @LEN = LEN(@Str)- @StartPoint + 1        END        INSERT INTO @Res VALUES(SUBSTRING(@Str,@StartPoint,@LEN))    END    RETURN ENDSELECT * FROM Split('A,B,CD,DF',',')

 

存储过程

CREATE PROCEDURE [dbo].[SplitStr]@Str VARCHAR(MAX),@Separator VARCHAR(10)AS BEGIN     DECLARE @Index INT    DECLARE @StartPoint INT    DECLARE @LEN INT                                                                                                                       DECLARE @T TABLE(        Strs VARCHAR(MAX)    )    DECLARE @Flag BIT    SET @Flag = 1    SET @Index = 0    WHILE @Flag <> 0    BEGIN        SET @StartPoint = @Index + 1        SET @Index = CHARINDEX(@Separator,@Str,@StartPoint)        SET @LEN = @Index - @StartPoint        IF @Index = 0         BEGIN            SET @Flag = 0            SET @LEN = LEN(@Str)- @StartPoint + 1        END        INSERT INTO @T VALUES(SUBSTRING(@Str,@StartPoint,@LEN))    END    SELECT * FROM @TENDEXEC SplitStr 'A,B,CD,EFG',','