SQL按指定字符拆分字符串

函数

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 
END

SELECT * 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 @T
END

EXEC SplitStr 'A,B,CD,EFG',','