先创建函数: create function P_Spilt(@S varchar(2000),@curPos int) returns varchar(500) as begin declare @iNum int declare @i as int declare @TempVale varchar(200) declare @retValue varchar(200) set @i=charindex('/',@s) set @iNum=1 while @i>0 begin set @TempVale=left(@S,@i-1) if @iNum=@curPos begin set @retValue=@TempVale set @i=-1 end else begin set @S=right(@S,len(@S)-@i) set @i=charindex('/',@S) set @iNum=@iNum+1 end end if (@retValue='' or @retValue is null) and @iNum=@curPos set @retValue=@s return @retValue
set strphoto=replace('upfile/2008-08-08/43456200808081213456.jpg','upfile/2008-08/43456200808081213456.jpg')
update photo
set strphoto=replace(strphoto,'upfile/2008-08-08/43456200808081213456.jpg','upfile/2008-08/43456200808081213456.jpg')
取的是今天的时间 如果要加2008-08-08的话就这样:
update photo set strphoto=strphoto+'?'+cast('2008-08-08' as varchar)
然后再按以下方式串起来 : 第一部分+ '/' + left(第二部分,7)+'/' + 第三部分
create function P_Spilt(@S varchar(2000),@curPos int)
returns varchar(500) as
begin
declare @iNum int
declare @i as int
declare @TempVale varchar(200)
declare @retValue varchar(200)
set @i=charindex('/',@s)
set @iNum=1
while @i>0
begin
set @TempVale=left(@S,@i-1)
if @iNum=@curPos
begin
set @retValue=@TempVale
set @i=-1
end
else
begin
set @S=right(@S,len(@S)-@i)
set @i=charindex('/',@S)
set @iNum=@iNum+1
end
end
if (@retValue='' or @retValue is null) and @iNum=@curPos
set @retValue=@s
return @retValue
end
然后调用
update photo set strphoto=dbo.P_Spilt(strphoto,1) + '/' + left(dbo.P_Spilt(strphoto,2),7) + '/' + dbo.P_Spilt(strphoto,3)
OK,确实是高手,搞定,给分,谢谢!另外再请教下,有一列几千条数据的格式多了个“/”,也即:/upfile/2008-08-08/43456200808081213456.jpg ,这要怎样改