自定义SQL的Split函数

 

-- =============================================
-- Author:  hy
-- Create date: 2008-12-03
-- Description: split函数
-- Debug:select * from dbo.Fun_Split('ABC:BC:C:D:E',':') 
-- =============================================
CREATE FUNCTION [dbo].[Fun_Split]
    (
      @SourceSql VARCHAR(8000) ,
      @StrSeprate VARCHAR(10)
    )
RETURNS @TEMP_Table TABLE ( a VARCHAR(100) )
AS 
    BEGIN
        DECLARE @i INT
        SET @SourceSql = RTRIM(LTRIM(@SourceSql))
        SET @i = CHARINDEX(@StrSeprate, @SourceSql)
        WHILE @i >= 1 
            BEGIN
                INSERT  @TEMP_Table
                VALUES  ( LEFT(@SourceSql, @i - 1) )
                SET @SourceSql = SUBSTRING(@SourceSql, @i + 1,
                                           LEN(@SourceSql) - @i)
                SET @i = CHARINDEX(@StrSeprate, @SourceSql)
            END
        IF @SourceSql <> '\' 
            INSERT  @TEMP_Table
            VALUES  ( @SourceSql )

        RETURN 
    END