SQL.字符串重叠项数量统计

Create Function CheckOverlap(
@SrcInfo nvarchar(max),
@DestInfo nvarchar(max),
@SplitStr nvarchar(100)
) returns int
as
begin
declare @LapNum int
Set @LapNum=0
--修正比对项
Set @SrcInfo=LTRIM(RTRIM(@SrcInfo))
Set @DestInfo=@SplitStr+LTRIM(RTRIM(@DestInfo))+@SplitStr
--分解识别
declare @ch nvarchar(1000)
declare @chidx integer
declare @splitLen integer
Set @splitLen=LEN(@SplitStr)
Set @chidx=CHARINDEX(@SplitStr,@SrcInfo)
while(@chidx>0)
begin
Set @ch=left(@SrcInfo,@chidx-1)
--
Set @SrcInfo=stuff(@SrcInfo,1,@chidx+@splitLen-1,'')
Set @chidx=CHARINDEX(@SplitStr,@SrcInfo)
--
Set @ch=@SplitStr+@ch+@SplitStr
if CHARINDEX(@ch,@DestInfo)>0
Set @LapNum=@LapNum+1
end
if @SrcInfo<>''
begin
Set @ch=@SplitStr+@SrcInfo+@SplitStr
if CHARINDEX(@ch,@DestInfo)>0
Set @LapNum=@LapNum+1
end
--
return @LapNum
end

 

 

--select dbo.CheckOverlap('aa,bb,cc,dd','a,aa,bbb,c,cc,ddd,eee',',')
--select dbo.CheckOverlap('ax,yb,cc,dd','ax,yax,ybb,c,cc,ddd,eee','x,y')