复制代码 代码如下:
—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
