如何批量替换数据库字段的内容

[brightview]

有一个字段全部存放文件名,例如1234.avi, 2345.mpg
现在我想用tsql语句批量替换为1234.wmv, 2345.wmv,该如何写?

[skyboy0720]

replace()

[老宛]

update yourtable set yourcol=replace(replace(yourcol,'.avi','.wmv'),'.mpg','.wmv')

[老宛]

--测试：
create table #t
(
yourcol varchar(20)
)
insert into #T select 'a.avi'
insert into #T select 'a.mpg'
insert into #T select 'a.wmv'
insert into #T select 'a.avi'
select * from #T
update #t set yourcol=replace(replace(yourcol,'.avi','.wmv'),'.mpg','.wmv')
select * from #T
drop table #t

[MY128]

update 表 set 字段 = '1234.wmv' from 表 where 字段='1234.avi'

update 表 set 字段 = '2345.wmv' from 表 where 字段='2345.wmv'

[lovebowl]

declare @d1 varchar(50)
set @d1 = '12345.svs'
print replace(@d1,right(@d1,len(@d1)-charindex('.',@d1)),'wmv')

[lovebowl]

update 表 set 字段= replace(字段,right(字段,len(字段)-charindex('.',字段)),'wmv')