SQL Server中TEXT/NTEXT字段内容替换方法总结

发布时间：2013-05-25 21:14:26 来源：51推一把

【摘要】SQL Server中TEXT/NTEXT字段内容替换方法总结(SQL 2005及以上建议使用VARCHAR(MAX)/NVARCHAR(MAX)代替) 1.update ntext:(1)varchar和nvarchar类型是支持replace，所以如果你的text/ntext不超过8000/4000可以先转换成前面两种类型再使用replace。

(SQL 2005及以上建议使用VARCHAR(MAX)/NVARCHAR(MAX)代替)

1.update ntext:
(1)varchar和nvarchar类型是支持replace，所以如果你的text/ntext不超过8000/4000可以先转换成前面两种类型再使用replace。
update 表名
set text类型字段名=replace(convert(varchar(8000),text类型字段名),要替换的字符,替换成的值)
update 表名
set ntext类型字段名=replace(convert(nvarchar(4000),ntext类型字段名),要替换的字符,替换成的值)
(2)如果text/ntext超过8000/4000,看如下例子
declare @pos int
declare @len int
declare @str nvarchar(4000)
declare @des nvarchar(4000)
declare @count int
set @des =<requested_amount+1>--要替换成的值
set @len=len(@des)
set @str= <requested_amount>--要替换的字符
set @count=0--统计次数.
WHILE 1=1
BEGIN
select @pos=patINDEX(%+@des+%,propxmldata) - 1
from 表名
where 条件
IF @pos>=0
begin
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(字段名)
from 表名
where 条件
UPDATETEXT 表名.字段名 @ptrval @pos @len @str
set @count=@count+1
end
ELSE
break;
END
select @count

(3)SQL存储过程批量替换ntext字段内容，@textA为要替换的字符串，@textB为替换后的字符串

–批量替换ntext字段内容，@textA为要替换的字符串，@textB为替换后的字符串
–exec P_replace_TableNTEXT ‘B_Goods’,G_Content’,GID’,’61.152.93.172:888′,’212.95.33.47′

create proc P_replace_TableNTEXT
(
@tableName varchar(50),
@ColNTEXT varchar(50),
@ColPrimaryKey varchar(50),
@textA nvarchar(500),
@textB nvarchar(500)
)
as
exec(
declare @str varbinary(16),@id int,@position int,@len int
set @len = datalength(+@textA+)
declare cursor_replace scroll Cursor
for select textptr(+@ColNTEXT+),+@ColPrimaryKey+ from +@tableName+
for read only
open cursor_replace
fetch next from cursor_replace into @str,@id
while @@fetch_status=0
begin
select @position=patindex(%+@textA+%,+@ColNTEXT+) from +@tableName+ where +@ColPrimaryKey+=@id
while @position>0
begin
set @position=@position-1
updatetext +@tableName+.+@ColNTEXT+ @str @position @len +@textB+
select @position=patindex(%+@textA+%,+@ColNTEXT+) from +@tableName+ where +@ColPrimaryKey+=@id
end
fetch next from cursor_replace into @str,@id
end
close cursor_replace
deallocate cursor_replace
)
go

2.alter column语句有局限性,比如不允许修改text、image、ntext 或 timestamp 列.
以下提供一个修改ntext列的例子:
Alter Table tbl Add newcol ntext null
go
update tbl set newcol=col
go
EXEC sp_rename tbl.col, oldcol, COLUMN
go
EXEC sp_rename tbl.newcol, col, COLUMN
go
alter table tbl drop column oldcol
go
以上通过新增一列替换旧的列方法实现了将一个不允许为空的ntext修改为允许为空的ntext列(注意:以上的go不能缺少).修改表结构之后,由于视图所依赖的基础对象的更改，视图的持久元数据会过期,需要刷新视图,通过sp_refreshview (可以通过sp_depends 找处相关的视图,再通过sp_refreshview逐个刷新).
另外可以也可以通过一下存储过程进行刷新所有视图:
PRINT Refreshing all views
DECLARE @vName sysname
DECLARE refresh_cursor CURSOR FOR
SELECT Name from sysobjects WHERE xtype = V
order by crdate
FOR READ ONLY
OPEN refresh_cursor
FETCH NEXT FROM refresh_cursor
INTO @vName
WHILE @@FETCH_STATUS <> -1
BEGIN
exec sp_refreshview @vName
PRINT 视图 + @vName + refreshed
FETCH NEXT FROM refresh_cursor
INTO @vName
END
CLOSE refresh_cursor
DEALLOCATE refresh_cursor

如何在sqlserver中实现text字段的特定字符串替换

--创建测试数据
create table t(id int,var1 text)
insert into t select 1,asdfasdfasdf<xxx>,s,fasdf(xxx),<xxx>a<xxx>a<xxx>asf

--执行更新操作
declare @str1 varchar(100),@str2 varchar(100)
declare @i int,@len int
declare @ptrval binary(16)
set @str1=<xxx>
set @str2=<yyyy>
set @len=len(@str1)

SELECT @i = patindex(%+@str1+%,var1)-1 from t WHERE id = 1

while @i > 0
begin
SELECT @ptrval = TEXTPTR(var1) FROM t WHERE id = 1
UPDATETEXT t.var1 @ptrval @i @len @str2
SELECT @i = patindex(%+@str1+%,var1)-1 from t WHERE id = 1
end
GO

--查看更新结果
select datalength(字段),* from t
/*
id var1
-- ------------------------------------------------------------------
1 asdfasdfasdf<yyyy>,s,fasdf(xxx),<yyyy>a<yyyy>a<yyyy>asf
*/

--删除测试数据
drop table t
go

当text列的数据长度小于8000字节时，可以直接使用replace()函数执行替换操作。