ALTER PROCEDURE [dbo].[UpdateAC_ArticleKeyWordsByKeyWords]
@KeyWords varchar(100)
AS
SET NOCOUNT OFF;
update AC_Article set KeyWords= case when substring(KeyWords,len(KeyWords),1)=','
then stuff(REPLACE(','+KeyWords,','+@KeyWords+',',','),1,1,'') else stuff(REPLACE(','+KeyWords,','+@KeyWords,''),1,1,'') end
--文章表
create table AC_Article
(
ArticleId int ,
KeyWords varchar(100)
)insert into AC_Article
select 1,'东风,商用' union
select 2,'依维柯,红岩' union
select 3,'欧曼, 东风 ' union
select 4,'北奔, 东风 '--我想传入一个东风,然后得到下面的效果
1 商用
2 依维柯,红岩
3 欧曼
4 北奔--不知道改怎么修改上面的存储过程了
@KeyWords varchar(100)
AS
SET NOCOUNT OFF;
update AC_Article set KeyWords= case when substring(KeyWords,len(KeyWords),1)=','
then stuff(REPLACE(','+KeyWords,','+@KeyWords+',',','),1,1,'') else stuff(REPLACE(','+KeyWords,','+@KeyWords,''),1,1,'') end
--文章表
create table AC_Article
(
ArticleId int ,
KeyWords varchar(100)
)insert into AC_Article
select 1,'东风,商用' union
select 2,'依维柯,红岩' union
select 3,'欧曼, 东风 ' union
select 4,'北奔, 东风 '--我想传入一个东风,然后得到下面的效果
1 商用
2 依维柯,红岩
3 欧曼
4 北奔--不知道改怎么修改上面的存储过程了
--先
replace(@KeyWords,' ','')
--再按你写的update
create table AC_Article
(
ArticleId int ,
KeyWords varchar(100)
)insert into AC_Article
select 1,'东风,商用' union
select 2,'依维柯,红岩' union
select 3,'欧曼, 东风 ' union
select 4,'北奔, 东风 '
goupdate AC_Article
set KeyWords = stuff(replace(',' + replace(KeyWords,' ',''),',东风',''),1,1,'')
select *
from AC_Articledrop table AC_ArticleArticleId KeyWords
----------- ----------------------------------------------------------------------------------------------------
1 商用
2 依维柯,红岩
3 欧曼
4 北奔(4 行受影响)
首先把你这个表规范一下,把里面的空格都替换掉
DROP TABLE AC_Article;
create table AC_Article
(
ArticleId int ,
KeyWords varchar(100)
)insert into AC_Article
select 1,'东风,商用' union
select 2,'依维柯,红岩' union
select 3,'欧曼, 东风 ' union
select 4,'北奔, 东风 '
declare @KeyWords varchar(20)
set @KeyWords='东风'while exists(select 1 from AC_Article where CHARINDEX(@KeyWords+',',replace(KeyWords,' ',''))>0)
update AC_Article set KeyWords=stuff(replace(KeyWords,' ',''),1,3,'')
where CHARINDEX(@KeyWords+',',replace(KeyWords,' ',''))>0while exists(select 1 from AC_Article where CHARINDEX(','+@KeyWords,replace(KeyWords,' ',''))>0)
update AC_Article set KeyWords=stuff(replace(KeyWords,' ',''),CHARINDEX(','+@KeyWords ,replace(KeyWords,' ','')),3,'')
where CHARINDEX(','+@KeyWords,replace(KeyWords,' ',''))>0
while exists(select 1 from AC_Article where CHARINDEX(','+@KeyWords+',',replace(KeyWords,' ',''))>0)
update AC_Article set KeyWords=stuff(replace(KeyWords,' ',''),CHARINDEX(@KeyWords+',',replace(KeyWords,' ','')),3,'')
where CHARINDEX(','+@KeyWords+',',replace(KeyWords,' ',''))>0
select * from AC_ArticleArticleId KeyWords
----------- ----------------------------------------------------------------------------------------------------
1 商用
2 依维柯,红岩
3 欧曼
4 北奔(4 row(s) affected)
所以比较复杂
比如 如果是: 商 用 车, 东风
--替换完之后因该是
商 用 车
--而不是
商用车
----------- -------------------------
1 东风 ,商 用 车
2 依维柯,红岩
3 欧曼, 东风
4 北奔, 东风
这个可用处理。如果下面这种情况也有的话,就不好处理了5 北奔, 东风 ,其他
create table AC_Article
(
ArticleId int ,
KeyWords varchar(100)
)insert into AC_Article
select 1,'东风,商用' union
select 2,'依 维 柯,红岩' union
select 3,'欧 曼, 东风 ' union
select 4,'北奔, 东风 '
goselect distinct t.ArticleId,substring(t.KeyWords,e.number,charindex(',',t.KeyWords + ',',e.number) - e.number)KeyWords
into #temp
from AC_Article t,spt_values e
where 1 = 1 and e.number between 1 and len(t.KeyWords)
and substring(',' + KeyWords,number,1) = ','update AC_Article
set KeyWords = stuff((select ',' + KeyWords from #temp where KeyWords not like '%东风%'
and ArticleId = AC_Article.ArticleId for xml path('')),1,1,'')select *
from AC_Articledrop table AC_Article,#temp
ArticleId KeyWords
----------- ----------------------------------------------------------------------------------------------------
1 商用
2 红岩,依 维 柯
3 欧 曼
4 北奔(4 行受影响)
create function getstr
(
@str varchar(1000),
@str1 varchar(100)
)
returns varchar(1000)
asbegin
set @str=@str+','
declare @key varchar(1000)
set @key=''
while charindex(',',@str)>0
begin
if replace(substring(@str,1,charindex(',',@str)-1),' ','')<>@str1
begin
set @key = @key +','+ substring(@str,1,charindex(',',@str)-1)
end
set @str=substring(@str,charindex(',',@str)+1,len(@str)-charindex(',',@str))
end
return stuff(@key,1,1,'')
endcreate table AC_Article
(
ArticleId int ,
KeyWords varchar(100)
)insert into AC_Article
select 1,'东风,商用' union
select 2,'依 维 柯,红岩' union
select 3,'欧 曼, 东风 ' union
select 4,'北奔, 东风 ' union
select 5,' 北奔, 东风 ,其他'
goselect ArticleId,dbo.getstr(KeyWords,'东风') from AC_Article/*
ArticleId
----------- --------------------
1 商用
2 依 维 柯,红岩
3 欧 曼
4 北奔
5 北奔,其他
表spt_values是自己创建的吧,那字段:number,代表是个数吗?我试用你的过程,没出来结果。