SqlServer Split Function

CREATE FUNCTION [dbo].[Split]

(

    @String VARCHAR(MAX),

    @Delimiter VARCHAR(5)

)

RETURNS @SplittedValues TABLE

(

    OccurenceId SMALLINT IDENTITY(1,1),

    SplitValue VARCHAR(MAX)

)

AS

BEGIN

    DECLARE @SplitLength INT

    WHILE LEN(@String) > 0

    BEGIN

        SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN

            LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END)

        INSERT INTO @SplittedValues

            SELECT SUBSTRING(@String,1,@SplitLength)

        SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0 THEN ''

            ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)

    END

    RETURN

END

转载于:https://www.cnblogs.com/Magicworks/archive/2010/07/30/1788631.html