CREATE function f_updateDate(@strq varchar(8000),@str varchar(20))
returns varchar(8000)
as
begin
if @strq=null or @strq=''
return(@str)
else
@strq=@strq+','+@str
return(@strq)
end还有我想写另一个函数
就是假如
传递(a,b)
a为 1,3,5
b为 3我要得到1,5
如果b为1
我要得到
3,5就是替换掉它
returns varchar(8000)
as
begin
if @strq=null or @strq=''
return(@str)
else
@strq=@strq+','+@str
return(@strq)
end还有我想写另一个函数
就是假如
传递(a,b)
a为 1,3,5
b为 3我要得到1,5
如果b为1
我要得到
3,5就是替换掉它
returns varchar(8000)
as
begin
declare @s varchar(8000)
if (@strq is null or @strq='')
set @s = @str
else
set @s=@strq+','+@str
return(@s)
end
returns varchar(8000)
as
begin
if @strq IS null or @strq=''
return(@str) else
set @strq=replace(@strq,@str,'')
return(@strq)
end
就是假如
传递(a,b)
a为 1,3,5
b为 3 我要得到1,5
如果b为1
我要得到
3,5
--
create function f_str(@s1 varchar(100),@s2 varchar(100))
returns varchar(100)
as
begin
declare @s varchar(100)
set @s = replace(@s1,','+@s2+',',',')
return @s
end
go
returns varchar(100)
as
begin
declare @s varchar(100)
set @s = replace(','+@s1+',',','+@s2+',',',')
return left(stuff(@s,1,1,'') ,len(@s)- 2)
end
go
select dbo.f_str('1,3,5','3')
select dbo.f_str('1,3,5','1')
/*
----------------------------------------------------------------------------------------------------
1,5(所影响的行数为 1 行)
----------------------------------------------------------------------------------------------------
3,5(所影响的行数为 1 行)*/
drop function f_str
CREATE function f_updateDate(@strq varchar(8000),@str varchar(20))
returns varchar(8000)
as
begin
set @strq= replace(@strq,@str+',','')
return @strq
end
go
select dbo. f_updateDate('1,3,5','1')
drop function dbo. f_updateDate --------------
3,5(1 row(s) affected
returns varchar(8000)
as
begin
set @strq= replace(@strq,@str+',','')
return @strq
end
go
select dbo. f_updateDate('1,3,5','5')
/*
-----------------
1,3,5 ==== *************(所影响的行数为 1 行)*/drop function dbo. f_updateDate
CREATE function f_updateDate(@strq varchar(8000),@str varchar(20))
returns varchar(8000)
as
begin
set @strq= replace(@strq,@str+',','')
return @strq
end
go
select dbo. f_updateDate('1,3,5','1')
drop function dbo. f_updateDate --------------
3,5 (1 row(s) affected
CREATE function f_updateDate(@strq varchar(8000),@str varchar(20))
returns varchar(8000)
as
begin
set @strq= replace(@strq,@str+',','')
return @strq
end
go
select dbo. f_updateDate('1,3,5','5')
/*
-----------------
1,3,5 ==== ************* (所影响的行数为 1 行) */ drop function dbo. f_updateDate
go
CREATE function f_updateDate(@strq varchar(8000),@str varchar(20))
returns varchar(8000)
as
begin
if isnull(@strq,'')=''
return(@str)
else
select @strq=replace(replace(','+@strq+',',','+@str+',',','),',,',',')
return(substring(@strq,2,len(@strq)-2))
endgo
select dbo.f_updateDate('1,3,5','3')
1,5(1 個資料列受到影響)
returns varchar(8000)
as
begin
declare @restr varchar(8000)
if @strq=null or @strq=''
set @restr=@str
else
if charindex(@str,@strq)>0
set @restr=@strq
else
set @restr=@strq+','+@str
return(@restr)
end
select dbo.f_updateDate(null,'1') 奇怪为什么不返回1 返回NULL啊?
returns varchar(8000)
as
begin
declare @restr varchar(8000)
if @strq is null or @strq=''
set @restr=@str
else
if charindex(@str,@strq)>0
set @restr=@strq
else
set @restr=@strq+','+@str
return(@restr)
end go
select dbo.f_updateDate(null,'1') drop function f_updateDate/*
----------------------------------
1(所影响的行数为 1 行)*/
===================================对 NULL的判断 改 is null
alter function f_updateDate(@strq varchar(8000),@str varchar(20))
returns varchar(8000)
as
begin
declare @restr varchar(8000)
if @strq is null or @strq='' --这里调整
set @restr=@str
else
if charindex(@str,@strq)>0
set @restr=@strq
else
set @restr=@strq+','+@str
return(@restr)
end