日期:2014-05-17 浏览次数:20576 次
create function f(@s nvarchar(100),@sCoe nvarchar(100))
returns varchar(100)
as
begin
declare @tmp nvarchar(50),@iBegin int ,@iLen int
set @s='<'+replace(@s,',','><')+'>'
set @sCoe='<'+replace(@sCoe,',','><')+'>'
set @iBegin=1
set @iLen=len(@s)
while(@iBegin<@iLen)
begin
if left(@s,1)='<'
begin
set @tmp=substring(@s,charindex('<',@s,@iBegin),charindex('>',@s,@iBegin))
set @sCoe=replace(@sCoe,@tmp,'')
set @iBegin=charindex('>',@s,@iBegin)+1
end
end
return replace(replace(replace(@sCoe,'><',''),'>',''),'<','')
end
go
create table ta
(id int ,
colle nvarchar(50)
)
insert into ta
select 1, '1,2'
union all
select 2,'2,3'
union all
select 3,'3,1'
go
select id,dbo.f(colle ,'1,2,3') from ta
drop table ta
drop function f
--我的和你的一样的,都是用函数,只是稍微改了一点点
create function f_split
(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)returns table
as
return
(
select Row_number()over(order by Number) rn ,
substring(@s,number,charindex(@split,@s+@split,number)-number)as col
from master..spt_values
where type='p' and number<=len(@s+'a')
and charindex(@split,@split+@s,number)=number
)
go
CREATE function ff(@sCoe nvarchar(100),@s nvarchar(100))
returns varchar(100)
as
begin
declare @St nvarchar(100)
select @St = isnull(@St,'')+ col+',' from dbo.f_split(@s,',') a
where not exists (select * from dbo.f_split(@sCoe,',') where col = a.col)
set @St = stuff(@St,len(@St),1,'')
return @St
end
go
create table #ta (id int ,colle nvarchar(50))
insert into #ta select 1, '1,2'
union all select 2,'2,3'
union all select 3,'3,1,4'
go
select id,dbo.ff(colle ,'1,2,3,6,7,4') from #ta
drop table #ta
drop function f