sqlserver 表函数问题
发布网友
发布时间：2022-12-21 19:56
共1个回答
热心网友
时间：2024-11-15 02:37
--通用,程序简单
Create Function fn_split3(@Val Varchar(4000))
Returns @Rst Table (Val Varchar(1000))
As
Begin
Declare @I Int
Set @I=1
While @I<=Len(@Val)
Begin
Insert Into @Rst Values(substring(@Val,@I,1))
Set @I=@I+1
End
Return
End
go
--通用,程序简单,按@sp分割字符串
Create Function fn_split4(@Val Varchar(4000),@sp Varchar(10))
Returns @Rst Table (Val Varchar(1000))
As
Begin
Declare @Tmp Varchar(4000)
Set @Tmp=@Val
While Charindex(@Sp,@Tmp)>0
Begin
Insert Into @Rst Values(substring(@Tmp,1,Charindex(@Sp,@Tmp)-1))
Set @Tmp=Stuff(@Tmp,1, Charindex(@Sp,@Tmp),'')
End
Insert Into @Rst Values(@Tmp)
Return
End
go
--用CTE递归表达式实现,(Sql2005或以上版本)
Create Function fn_split2(@Val Varchar(4000))
Returns @Rst Table (Val Varchar(1000))
As
Begin
With T
As
(Select Convert(Varchar(4000),@Val) As Val,1 As Cnt
Union All
Select Convert(Varchar(4000),substring(@Val,Cnt,1)) As Val,Cnt+1 As Cnt From T Where Cnt<Len(@Val)
)
Insert Into @Rst
Select Val from T Where Cnt>1
Option (MaxRecursion 4000)
Return
End
go
--用C#创建的,效率最高,这里字数有限,代码贴不上来
追问谢谢， C#,java ,Oracle我都会，就是不会sqlserver.
