代码
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
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
( 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
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
CSDN提问:
如何在sqlserver中实现text字段的特定字符串替换
就是将text字段里面的所有如“<xxx>” 替换为 “<yyy>”, 这个在sqlserver里操作起来好像很麻烦,请帮忙,谢先!
代码
--
创建测试数据
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 * from t
/*
id var1
-- ------------------------------------------------------------------
1 asdfasdfasdf<yyyy>,s,fasdf(xxx),<yyyy>a<yyyy>a<yyyy>asf
*/
-- 删除测试数据
drop table t
go
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 * from t
/*
id var1
-- ------------------------------------------------------------------
1 asdfasdfasdf<yyyy>,s,fasdf(xxx),<yyyy>a<yyyy>a<yyyy>asf
*/
-- 删除测试数据
drop table t
go
当text列的数据长度小于8000字节时,可以直接使用replace()函数执行替换操作。