sql ntext数据类型字符替换实现代码

复制代码  代码如下:

—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