SQL 分隔字串

標籤：

ALTER FUNCTION dbo.fn_Split(    @Expression    VARCHAR(8000),    @Delimiter    VARCHAR(10))RETURNS @table_Value    TABLE(    SortNo    INT IDENTITY(1,1) NOT NULL,    Value    VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ‘‘)ASBEGIN    --============ DECLARE LOCAL VARIABLE TO USE  ===============================    DECLARE @intEndpos    BIGINT    DECLARE @strTemp        VARCHAR(100)     --============ INITIALIZE  THE VALUE OF VARIABLE ==============================    SET @intEndpos = 0    SET @strTemp = ‘‘    --============= CHECK INPUT DATA VALIDATE ==========================    IF LEN( LTRIM(RTRIM(@Expression ) ) ) = 0    BEGIN        RETURN    END    IF LEN(@Delimiter) = 0    BEGIN        RETURN    END    --============= Split and INSERT ========================================    IF RIGHT ( @Expression, 1) = @Delimiter        SET @Expression = LEFT ( @Expression , LEN (@Expression) -1 )    WHILE LEN(@Expression) > 0    BEGIN        SET @intEndpos = CHARINDEX(@Delimiter,@Expression,1)         IF @intEndpos > 0        BEGIN            SET @strTemp = SUBSTRING(@Expression , 1 , @intEndpos -1)            INSERT INTO @table_Value(Value) VALUES (@strTemp)            SET @Expression = SUBSTRING ( @Expression , @intEndpos + 1, LEN (@Expression) - @intEndpos)        END        ELSE        BEGIN            INSERT INTO @table_Value(Value) VALUES (@Expression)            SET @Expression = ‘‘        END    END    RETURNEND

 

SQL 分隔字串