在mssqlserver中 自定义函数 使用游标 截取字符串并且返回一个sql语句

if exists(select * from sys.objects where name='f_splitstr')

drop function f_splitstr

go

create function f_splitstr

(

@SourceSql varchar(8000),

@strSeprate varchar(100)

)

returns nvarchar(300)

as

begin

declare @ch as varchar(100)

declare @temptable table(cardtype nvarchar(10))

declare @sql nvarchar(300)

declare @value nvarchar(8)

declare @count int

declare @tempcount int

 

set @sql=''

set @value=''

set @tempcount=0

 

set @SourceSql=@SourceSql+@StrSeprate

while(@SourceSql<>'')

begin

set

@ch=left(@SourceSql,Charindex(',',@SourceSql,1)-1)

insert @temptable values(@ch)

set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'')

end

 

select @count=count(*) from @temptable;

 

--create cursor

declare @each_valueCursor cursor

 

--设定游标欲操作的数据

set @each_valueCursor=cursor for

select cardtype from @temptable

 

--打开游标

open @each_valueCursor

 

fetch next from @each_valueCursor into @value

while(@@fetch_status=0)

begin

set @tempcount=@tempcount+1;

 

if(@tempcount<>@count)

set @sql=@sql+' patindex(''%'+@value+'%'',cardtype)>0 or'

else

set @sql=@sql+' patindex(''%'+@value+'%'',cardtype)>0'

fetch next from @each_valueCursor into @value

end

 

--关闭游标

close @each_valueCursor

--删除游标

deallocate @each_valueCursor

return @sql

end

go

 

 

select dbo.f_splitstr('37710620,49210397,12345678',',')

 

返回类似
patindex('%37710620%',cardtype)>0 or patindex('%49210397%',cardtype)>0 or patindex('%12345678%',cardtype)>0