怎样在每个字段的固定位置加上一段字符？

Question

原始表id   path
1     picture/phone/e1.jpg
2     picture/phone/d3.jpg
3     picture/clothes/t1.jpg
4     picture/clothes/1.jpg我要变成
id   path
1     picture/phone/Thumbnail/e1.jpg
2     picture/phone/Thumbnail/d3.jpg
3     picture/clothes/Thumbnail/t1.jpg
4     picture/clothes/Thumbnail/1.jpg就是在第二个/后加上"Thumbnail/"考虑一下效率

Answer 1

update tb
set path=replace(path,'picture/phone/','picture/phone/Thumbnail/')

Answer 2

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[path] varchar(50))
insert [tb]
select 1,'picture/phone/e1.jpg' union all
select 2,'picture/phone/d3.jpg' union all
select 3,'picture/clothes/t1.jpg' union all
select 4,'picture/clothes/1.jpg'
update tb
set path=replace(path,'picture/phone/','picture/phone/Thumbnail/')
select * from [tb]
--测试结果:
/*
id          path                                               
----------- -------------------------------------------------- 
1           picture/phone/Thumbnail/e1.jpg
2           picture/phone/Thumbnail/d3.jpg
3           picture/clothes/t1.jpg
4           picture/clothes/1.jpg（所影响的行数为 4 行）
*/

Answer 3

1> declare @DocumentSummary varchar(20);
2> set @DocumentSummary='picture/phone/e1.jpg';
3> select left(@DocumentSummary,CHARINDEX('/', @DocumentSummary,CHARINDEX('/', @
DocumentSummary)+1))
4>      +'Thumbnail/'+
5>      SUBSTRING ( @DocumentSummary ,CHARINDEX('/', @DocumentSummary,CHARINDEX(
'/', @DocumentSummary)+1)+1 , 100 );
6> go----------------------------------------
picture/phone/Thumbnail/e1.jpg(1 rows affected)
1>

Answer 4

错了，修正一下
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[path] varchar(50))
insert [tb]
select 1,'picture/phone/e1.jpg' union all
select 2,'picture/phone/d3.jpg' union all
select 3,'picture/clothes/t1.jpg' union all
select 4,'picture/clothes/1.jpg'
update tb
set path=stuff(path,len(path)-charindex('/',reverse(path))+1,0,'/Thumbnail')
select * from [tb]
--测试结果:
/*
id          path                                               
----------- -------------------------------------------------- 
1           picture/phone/Thumbnail/e1.jpg
2           picture/phone/Thumbnail/d3.jpg
3           picture/clothes/Thumbnail/t1.jpg
4           picture/clothes/Thumbnail/1.jpg（所影响的行数为 4 行）*/

Answer 5

--> 测试时间：2009-12-14 23:12:22--> 我的淘宝：《戒色坊》http://shop36766744.taobao.com/if object_id('[TB]') is not null drop table [TB]
create table [TB]([id] int,[path] varchar(22))
insert [TB]
select 1,'picture/phone/e1.jpg' union all
select 2,'picture/phone/d3.jpg' union all
select 3,'picture/clothes/t1.jpg' union all
select 4,'picture/clothes/1.jpg'select id,path=stuff(path,len(path)-charindex('/',reverse(path)),1,'/Thumbnail')
from TB
/*id          path                                                                                                                                                                                                                                                             
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
1           picture/phon/Thumbnail/e1.jpg
2           picture/phon/Thumbnail/d3.jpg
3           picture/clothe/Thumbnail/t1.jpg
4           picture/clothe/Thumbnail/1.jpg（所影响的行数为 4 行）*/drop table [TB]

Answer 6

create table tb(id int, path varchar(50))
insert into tb values(1 ,   'picture/phone/e1.jpg') 
insert into tb values(2 ,   'picture/phone/d3.jpg') 
insert into tb values(3 ,   'picture/clothes/t1.jpg') 
insert into tb values(4 ,   'picture/clothes/1.jpg')
goselect id , 
       reverse(left(reverse(path),charindex('/',reverse(path))) + 
       reverse('Thumbnail') + 
       substring(reverse(path),charindex('/',reverse(path)) ,len(path)))
from tbdrop table tb /*
id                                        
----------- -------------------------------
1           picture/phone/Thumbnail/e1.jpg
2           picture/phone/Thumbnail/d3.jpg
3           picture/clothes/Thumbnail/t1.jpg
4           picture/clothes/Thumbnail/1.jpg（所影响的行数为 4 行）
*/

Answer 7

select id,left(path,CHARINDEX('/', path,CHARINDEX('/', path)+1))
+'Thumbnail/'+
SUBSTRING ( path ,CHARINDEX('/', path,CHARINDEX('/', path)+1)+1 , 100 )
from 原始表update 原始表
set path=left(path,CHARINDEX('/', path,CHARINDEX('/', path)+1))
+'Thumbnail/'+
SUBSTRING ( path ,CHARINDEX('/', path,CHARINDEX('/', path)+1)+1 , 100 )

Answer 8

乌龟，不行啊IErrorInfo.GetDescription 因 E_FAIL(0x80004005) 而失败

Answer 9

参照改了一下，还是参数有点问题
left(picture,INSTR( picture,INSTR(picture,'/')+1,'/'))+'Thumbnail/'+mid( picture ,INSTR(picture,INSTR( picture ,'/')+1)+1 ,'/', 100 )

Answer 10

access
set path=left(path,instr('/', path,instr('/', path)+1))
    +'Thumbnail/'+
    MID ( path ,instr('/', path,instr('/', path)+1)+1 , 100 )

Answer 11

思路就是先反序字符串,找到第一个/的位置,然后将这个值替换成/Thumbnail/然后在反序.
select id , 
       reverse(left(reverse(path),charindex('/',reverse(path))) + 
       reverse('Thumbnail') + 
       substring(reverse(path),charindex('/',reverse(path)) ,len(path)))
from tb