create table #t(txt text)insert into #t values('asdfa$$$ljljl$$$1234$$$') insert into #t values('$$$123$$') insert into #t values('34$$$as')goDECLARE @val varbinary(16) declare @n int declare @len int declare @old varchar(8000) declare @new varchar(8000)set @old = '$$$' set @new = '@@' set @len = len(@old) while 1=1 begin SELECT top 1 @val = TEXTPTR(txt),@n = patindex('%'+@old+'%',txt)-1 from #t where patindex('%'+@old+'%',txt) > 0 if @@rowcount = 0 break updatetext #t.txt @val @n @len @new end
goselect * from #t --asdfa@@ljljl@@1234@@ --@@123$$ --34@@as但是,如果将'$$$'转成'$$'则可能会出问题。自己再想想,我没时间了:)
好像可以的啊!create table #t(txt text)insert into #t values('asdfa$$$$ljljl$$$$1234$$$$') insert into #t values('$$$$123$$') insert into #t values('34$$$$as')goDECLARE @val varbinary(16) declare @n int declare @len int declare @old varchar(8000) declare @new varchar(8000)set @old = '$$$$' set @new = '$$' set @len = len(@old) while 1=1 begin SELECT top 1 @val = TEXTPTR(txt),@n = patindex('%'+@old+'%',txt)-1 from #t where patindex('%'+@old+'%',txt) > 0 if @@rowcount = 0 break updatetext #t.txt @val @n @len @new end goselect * from #t drop table #t
MYTEXT字段为Text型
UPDATE TABLE1
SET MYTEXT = REPLACE(CONVERT(VARCHAR,MYTEXT),'AA','BB')
insert into #t values('$$$123$$')
insert into #t values('34$$$as')goDECLARE @val varbinary(16)
declare @n int
declare @len int
declare @old varchar(8000)
declare @new varchar(8000)set @old = '$$$'
set @new = '@@'
set @len = len(@old)
while 1=1
begin
SELECT top 1 @val = TEXTPTR(txt),@n = patindex('%'+@old+'%',txt)-1
from #t
where patindex('%'+@old+'%',txt) > 0
if @@rowcount = 0
break
updatetext #t.txt @val @n @len @new
end
goselect * from #t
--asdfa@@ljljl@@1234@@
--@@123$$
--34@@as但是,如果将'$$$'转成'$$'则可能会出问题。自己再想想,我没时间了:)
insert into #t values('$$$$123$$')
insert into #t values('34$$$$as')goDECLARE @val varbinary(16)
declare @n int
declare @len int
declare @old varchar(8000)
declare @new varchar(8000)set @old = '$$$$'
set @new = '$$'
set @len = len(@old)
while 1=1
begin
SELECT top 1 @val = TEXTPTR(txt),@n = patindex('%'+@old+'%',txt)-1
from #t
where patindex('%'+@old+'%',txt) > 0
if @@rowcount = 0
break
updatetext #t.txt @val @n @len @new
end
goselect * from #t
drop table #t