复制代码 代码如下:
—ntext数据类型字符替换
create table tt
(
sid int identity(1,1),
cont ntext
)
go
insert into tt(cont) values(n’fd sad fdsa 涂聚文工团 缔友计算机信息技术有限公司 可能性 桔柑 ‘)
go
update tt set cont=’fd sad fdsa 涂聚文工团 可能性 缔友计算机信息技术有限公司 桔柑 ‘ where sid=1
–去空格
exec sp_dboption ‘pubs’, ‘select into/bulkcopy’, ‘true’
–sp_dboption ‘pubs’, ‘select into/bulkcopy’, ‘true’
go
declare @ptrval binary(16),@i int,@k int,@f int
select @i=datalength(cont) from tt where sid=1
set @k=1
while @i>1
begin
select @f=charindex(space(1),cont) from tt where sid=1 —space(1)
set @f=@f-1
select @ptrval = textptr(cont) from tt where sid=1
if @f>0
updatetext tt.cont @ptrval @f 1 null
if @k=@i
break
else
set @k=@k+1
continue
end
go
–sp_dboption ‘pubs’, ‘select into/bulkcopy’, ‘false’
exec sp_dboption ‘pubs’, ‘select into/bulkcopy’, ‘false’
go
select * from tt
—换英文字母
exec sp_dboption ‘pubs’, ‘select into/bulkcopy’, ‘true’
go
declare @ptrval binary(16),@i int,@k int,@f int
select @i=datalength(cont) from tt where sid=1
set @k=1
while @i>1
begin
select @f=charindex(‘sad’,cont) from tt where sid=1 —space(1)
set @f=@f-1
select @ptrval = textptr(cont) from tt where sid=1
if @f>0
updatetext tt.cont @ptrval @f 3 ‘ggg’
if @k=@i
break
else
set @k=@k+1
continue
end
go
select * from tt
—中文字母
exec sp_dboption ‘pubs’, ‘select into/bulkcopy’, ‘true’
go
declare @ptrval binary(16),@i int,@k int,@f int
select @i=datalength(cont) from tt where sid=1
set @k=1
while @i>1
begin
select @f=charindex(‘涂聚文’,cont) from tt where sid=1 —space(1)
set @f=@f-1
select @ptrval = textptr(cont) from tt where sid=1
if @f>0
updatetext tt.cont @ptrval @f 6 ‘涂斯博’
if @k=@i
break
else
set @k=@k+1
continue
end
go
select * from tt
exec sp_dboption ‘pubs’, ‘select into/bulkcopy’, ‘true’
go
declare @ptrval binary(16),@i int,@k int,@f int,@change nvarchar(20),@newstr nvarchar(20),@len int
set @change=n’sad’ –要替换的字符
–set @len=datalength(@change)
set @len=len(@change)
set @newstr=n’ggg’ –替换成的字符
select @i=datalength(cont) from tt where sid=1
set @k=1
while @i>1
begin
select @f=charindex(@change,cont) from tt where sid=1 —space(1)
set @f=@f-1
select @ptrval = textptr(cont) from tt where sid=1
if @f>0
updatetext tt.cont @ptrval @f @len @newstr
if @k=@i
break
else
set @k=@k+1
continue
end
go
select * from tt
exec sp_dboption ‘pubs’, ‘select into/bulkcopy’, ‘true’
go
declare @ptrval binary(16),@i int,@k int,@f int,@change nvarchar(20),@newstr nvarchar(20),@len int
set @change=n’涂聚文’ –要替换的字符
–set @len=datalength(@change) –用此会出错
set @len=len(@change)
set @newstr=n’涂斯博’ –替换成的字符
select @i=datalength(cont) from tt where sid=1
set @k=1
while @i>1
begin
select @f=charindex(@change,cont) from tt where sid=1 —space(1)
set @f=@f-1
select @ptrval = textptr(cont) from tt where sid=1
if @f>0
updatetext tt.cont @ptrval @f @len @newstr
if @k=@i
break
else
set @k=@k+1
continue
end
go
select * from tt
update tt set cont=’fd sad fdsa 涂聚文工团 可能性 缔友计算机信息技术有限公司 桔柑 ‘ where sid=1
select datalength(‘涂聚文’)
select len(‘涂聚文’)
select datalength(‘sad’)
select len(‘sad’)
select len(space(1))
exec sp_dboption ‘pubs’, ‘select into/bulkcopy’, ‘true’
declare @ptrval binary(16),@i int,@k int,@f int,@change nvarchar(20),@newstr nvarchar(20),@len int
set @change=n’涂聚文’ –要替换的字符
–set @len=datalength(@change) –用此会出错
set @len=len(@change)
set @newstr=n’涂斯博’ –替换成的字符
select @i=datalength(cont) from tt where sid=1
set @k=1
while @i>1
begin
select @f=charindex(@change,cont) from tt where sid=1 —space(1)
set @f=@f-1
select @ptrval = textptr(cont) from tt where sid=1
if @f>0
updatetext tt.cont @ptrval @f @len @newstr
if @k=@i
break
else
set @k=@k+1
continue
end
go
exec sp_dboption ‘pubs’, ‘select into/bulkcopy’, ‘true’
declare @ptrval binary(16),@i int,@k int,@f int,@change nvarchar(20),@newstr nvarchar(20),@len int
set @change=n’涂聚文’ –要替换的字符
–set @len=datalength(@change) –用此会出错
set @len=len(@change)
set @newstr=n’涂斯博’ –替换成的字符
select @i=datalength(cont) from tt where sid=1
set @k=1
while @i>1
begin
select @f=charindex(@change,cont) from tt where sid=1 —space(1)
set @f=@f-1
select @ptrval = textptr(cont) from tt where sid=1
if @f>0
updatetext tt.cont @ptrval @f @len @newstr
if @k=@i
break
else
set @k=@k+1
continue
end
go