有表[Movie_Movie]如下:type
恐怖/惊悚
惊悚/犯罪
冒险/惊悚/科幻/剧情
恐怖/惊悚片怎么用update 语句将type里面的惊悚该成惊悚片
而恐怖/惊悚片不变本人写的如下 不可用
update [Movie_Movie] set Type = replace
((select type from Movie_Movie as mm1 where mm1.id=[Movie_Movie].id),'惊秫','惊悚片')
恐怖/惊悚
惊悚/犯罪
冒险/惊悚/科幻/剧情
恐怖/惊悚片怎么用update 语句将type里面的惊悚该成惊悚片
而恐怖/惊悚片不变本人写的如下 不可用
update [Movie_Movie] set Type = replace
((select type from Movie_Movie as mm1 where mm1.id=[Movie_Movie].id),'惊秫','惊悚片')
set Type = replace(type,'惊秫','惊悚片')
where charindex('惊悚片',type)<0
set Type = replace(type,'惊悚','惊悚片')
where charindex('惊悚片',type)<0
declare @s table (type varchar(50))
insert into @s
select '恐怖/惊悚' union all
select '惊悚/犯罪' union all
select '冒险/惊悚/科幻/剧情' union all
select '恐怖/惊悚片'update @s set type=stuff(type,charindex('/惊悚/','/'+type+'/'),len('惊悚'),'惊悚片')
where charindex('/惊悚/','/'+type+'/')>0select * from @s
update [Movie_Movie] set Type = replace(type,'惊秫','惊悚片')
where charindex('惊悚片',type)=0 and charindex('惊悚',type)>0
update [Movie_Movie]
set Type=replace(Type,'惊秫','惊悚片')
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-08-11 15:49:01
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([type] varchar(100))
insert [tb]
select '恐怖/惊悚' union all
select '惊悚/犯罪' union all
select '冒险/惊悚/科幻/剧情' union all
select '恐怖/惊悚片'
--------------开始查询--------------------------
select * from tbupdate
tb
set
type=stuff(type,charindex('/惊悚/','/'+type+'/'),len('惊悚'),'惊悚片')
where
charindex('/惊悚/','/'+type+'/')>0select * from tb
----------------结果----------------------------
/*(所影响的行数为 4 行)type
----------------------------------------------------------------------------------------------------
恐怖/惊悚
惊悚/犯罪
冒险/惊悚/科幻/剧情
恐怖/惊悚片(所影响的行数为 4 行)
(所影响的行数为 3 行)type
----------------------------------------------------------------------------------------------------
恐怖/惊悚片
惊悚片/犯罪
冒险/惊悚片/科幻/剧情
恐怖/惊悚片(所影响的行数为 4 行)*/
INSERT @TB
SELECT N'恐怖/惊悚' UNION ALL
SELECT N'惊悚/犯罪' UNION ALL
SELECT N'冒险/惊悚/科幻/剧情' UNION ALL
SELECT N'恐怖/惊悚片'update @TB set Type=reverse(stuff(reverse(replace(Type+'/', '惊悚/', '惊悚片/')),1,1,''))SELECT *
FROM @TB
/*type
---------------
恐怖/惊悚片
惊悚片/犯罪
冒险/惊悚片/科幻/剧情
恐怖/惊悚片*/
declare @a table([type] varchar(400))
insert @a select
'恐怖/惊悚' union all select
'惊悚/犯罪' union all select
'冒险/惊悚/科幻/剧情'union all select
'恐怖/惊悚片'
/*
update @a
set [type]*/update @a
set [type]=replace([type],'惊悚','惊悚片') where charindex('/惊悚/','/'+type+'/')>0select * from @a type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
恐怖/惊悚片
惊悚片/犯罪
冒险/惊悚片/科幻/剧情
恐怖/惊悚片(4 行受影响)
set Type = replace(type,'惊秫','惊悚片')
where charindex('/惊悚/','/'+type+'/')>0
晕create TABLE [Movie_Movie] ([type] NVARCHAR(15))
INSERT [Movie_Movie]
SELECT N'恐怖/惊悚' UNION ALL
SELECT N'惊悚/犯罪' UNION ALL
SELECT N'冒险/惊悚/科幻/剧情' UNION ALL
SELECT N'恐怖/惊悚片'
update [Movie_Movie]
set Type = replace(type,'惊悚','惊悚片')
where charindex('惊悚片',type)=0
select * from [Movie_Movie]
/*
type
---------------
恐怖/惊悚片
惊悚片/犯罪
冒险/惊悚片/科幻/剧情
恐怖/惊悚片*/