数据库字段数据修改有关问题_Sql Server

数据库字段数据修改问题
数据库字段 pic
里面的数据有
20054181241305.jpg
/productFiles/2007-10/20071012154826320.gif
怎么把里面的的数据改为这样的格式啊
/productFiles/20054181241305.jpg
/productFiles/20071012154826320.gif

就是加上路径，去掉日期
怎么用SQL语句修改。。

------解决方案--------------------

SQL code

create table wan(pic varchar(100))insert into wanselect '20054181241305.jpg' union allselect '/productFiles/2007-10/20071012154826320.gif'update wan set pic='/productFiles/'+reverse(substring(reverse(pic),1,len(pic)-charindex('/',reverse(pic),1)))select * from wan/*pic---------------------------------------/productFiles/20054181241305.jpg/productFiles/20071012154826320.gif(2 row(s) affected)*/
------解决方案--------------------
SQL codecreate table ta(pic varchar(128))insert ta select '20054181241305.jpg' union select '/productFiles/2007-10/20071012154826320.gif'select * from ta /*pic20054181241305.jpg/productFiles/2007-10/20071012154826320.gif  */update ta set pic =case when CHARINDEX('/',pic,0)=0 then '/productFiles/'+pic  else '/productFiles/'+reverse(substring(reverse(pic),1,charindex('/',reverse(pic),1)-1)) end /*pic/productFiles/20054181241305.jpg/productFiles/20071012154826320.gif  */drop table ta