拆下表函数写法及用例
一、写法
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<>''
所有问题都会有一定程度的抽象和假设