一个对字符串进行分割,返回一个临时表的表函数实现(转)

1.表函数定义.

 

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnSplit]
    (
      @InputList VARCHAR(MAX) -- List of delimited items
      , @Delimiter VARCHAR(50) = '{,}' -- delimiter that separates items
      , @NeedTrim BIT = 1
      , @RemoveEmptyEntries BIT = 1
    )
RETURNS @List TABLE ( Item VARCHAR(MAX) )
BEGIN
    DECLARE @sItem VARCHAR(MAX)
    WHILE CHARINDEX(@Delimiter, @InputList, 0) <> 0
        BEGIN
            SELECT  @sItem = RTRIM(LTRIM(SUBSTRING(@InputList, 1,
                                                   CHARINDEX(@Delimiter, @InputList, 0)
                                                   - 1))),
                    @InputList = RTRIM(LTRIM(SUBSTRING(@InputList,
                                                       CHARINDEX(@Delimiter, @InputList, 0)
                                                       + LEN(@Delimiter),
                                                       LEN(@InputList))))
 
            IF LEN(@sItem) > 0
                BEGIN
                    IF @NeedTrim = 1
                        BEGIN
                            SET @sItem = LTRIM(RTRIM(@sItem))
                        END
                    IF @RemoveEmptyEntries <> 1
                        OR @sItem <> ''
                        BEGIN
                            INSERT  INTO @List
                                    SELECT  @sItem                       
                        END
                END     
        END

    IF LEN(@InputList) > 0
        INSERT  INTO @List
                SELECT  @InputList -- Put the last item in
    RETURN
END

 

GO

 

2.表函实现:

 

select * from dbo.fnSplit('123,456',',',0,0)