declare @t table(path varchar(100)) insert into @t select '/1111/2222/3333/' union all select '/11/22/333' union all select '/23234/454545'update @t set path=replace(path,'1111','88888888')select * from @t
update tablex set col = '/' + '88888888' + substring(col,4,len(col)) where substring(col,2,2) = '11'
declare @t table(path varchar(100)) insert into @t select '/1111/2222/3333/' union all select '/11/22/333' union all select '/23234/454545'update @t set path=replace(path,'/11/','/88888888/')select * from @t
create table aaa(name varchar(100)) insert into aaa select ' /1111/2222/3333/' union all select '/11/22/333' union all select ' /23234/454545'update aaa set name=replace(name,'/1111/','/8888/') where charindex('/1111/',name)>0
update 表名 set path=replace(path,'11','88888888') where charindex('/11/','/'+col+'/')>0
declare @t table(path varchar(100)) insert into @t select '/1111/2222/3333/' union all select '/11/22/333/' union all select '/23234/454545/11/'update @t set path=replace(path,'/11/','/88888888/')select * from @t
update 表名 set path=replace(path,'11','88888888') where patindex('%/11/%','/'+path+'/')>0
to itblog(^ω^) oracle 里面没有charindex,patindex这两个方法啊!
update aaa set name=replace(name,'/1111/','/88888888/')就行了
oracle没弄过,你试一下这个函数~INSTR(,[,<i>[,]]) c1,c2均为字符串,i,j为整数。函数返回c2在c1中第j次出现的位置,搜索从c1的第i个字符开始。当没有发现需要的字符时返回0,如果i为负数,那么搜索将从右到左进行,但是位置的计算还是从左到右,i和j的缺省值为1. select INSTR('Mississippi','i',3,3) from dualINSTR('MISSISSIPPI','I',3,3)11select INSTR('Mississippi','i',-2,3) from dualINSTR('MISSISSIPPI','I',3,3)2
/1111/2222/3333/
/88888888/22/333
/23234/454545要求结果是这样的
insert into @t select '/1111/2222/3333/'
union all select '/11/22/333'
union all select '/23234/454545'update @t set path=replace(path,'1111','88888888')select * from @t
set col = '/' + '88888888' + substring(col,4,len(col))
where substring(col,2,2) = '11'
insert into @t select '/1111/2222/3333/'
union all select '/11/22/333'
union all select '/23234/454545'update @t set path=replace(path,'/11/','/88888888/')select * from @t
insert into aaa
select ' /1111/2222/3333/'
union all
select '/11/22/333'
union all
select ' /23234/454545'update aaa
set name=replace(name,'/1111/','/8888/')
where charindex('/1111/',name)>0
insert into @t select '/1111/2222/3333/'
union all select '/11/22/333/'
union all select '/23234/454545/11/'update @t set path=replace(path,'/11/','/88888888/')select * from @t
set name=replace(name,'/1111/','/88888888/')就行了
c1,c2均为字符串,i,j为整数。函数返回c2在c1中第j次出现的位置,搜索从c1的第i个字符开始。当没有发现需要的字符时返回0,如果i为负数,那么搜索将从右到左进行,但是位置的计算还是从左到右,i和j的缺省值为1.
select INSTR('Mississippi','i',3,3) from dualINSTR('MISSISSIPPI','I',3,3)11select INSTR('Mississippi','i',-2,3) from dualINSTR('MISSISSIPPI','I',3,3)2