[导入]修改图片路径的存储过程

wangdetian168

于 2010-09-30 12:22:00 发布

阅读量148

点赞数

文章标签： 360 Go HTML

本文介绍了一个SQL过程，用于批量更新数据库中产品表里的图片路径。通过使用动态SQL语句，可以将包含特定年月的图片路径统一替换为空字符串，然后再进一步标准化这些路径。

alter proc replaceImgPath(@yearmonth varchar(36))
as
begin
declare @sqlone varchar(360)
declare @sqltwo varchar(360)
--exec('select picpath1,replace(picpath1,''/upload/UploadFile/'+@yearmonth+'/'','''') from product where len(picpath1)>0 and picpath1 like ''%'+@yearmonth+'%''')
---修改路径
set @sqlone='update product set picpath1=replace(picpath1,''/upload/UploadFile/'+@yearmonth+'/'','''') where len(picpath1)>0 and picpath1 like ''%'+@yearmonth+'%'''
exec(@sqlone)
---修改为标准路径
set @sqltwo='update product set picpath1=''/upload/UploadFile/'+@yearmonth+'/''+substring(picpath1,charindex(''/'',picpath1)+1,len(picpath1)) where len(picpath1)>0 and picpath1 like ''%'+@yearmonth+'%'' and len(picpath1)<36' --order by len(picpath1)
exec(@sqltwo)
end
go

文章来源: http://www.cnblogs.com/wangdetian168/archive/2008/10/16/1312703.html