拆下表函数写法及用例

一、写法

CREATE function [dbo].[split](@str nvarchar(4000),@splitstr nvarchar(10))
    returns @temp table([splitcolumn] nvarchar(2000))
as 
begin
    declare @i int
    set @str=rtrim(ltrim(@str))
    set @i=charindex(@splitstr,@str)
    while @i>=1
    begin
        insert @temp values(ltrim(rtrim(left(@str,@i-1))))
        set @str=substring(@str,@i+1,len(@str)-@i)
        set @i=charindex(@splitstr,@str)
    end
    if @str<>''
    insert @temp values(@str)
    return 
end

GO

用例：

 SELECT  T1.sys_guid AS main_id, @arg1 AS authField,splitcolumn AS authValue
                                                    FROM Table  AS T1
                                                    OUTER APPLY(SELECT splitcolumn FROM dbo.split(T1.Col_003,'-') ) AS T2
                                                    WHERE T1.Col_003<>''