SQL SERVER 关于 NTEXT 字段修改的问题! 先用ansireplacetext函数把双引号字符替换掉再用replace,完了之后再用ansireplacetext八双引号替换回来 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 存HTML代码的话,最好还是通过html编辑器修改,后台数据库修改很不方便 楼主可以在这里得到答案>2http://community.csdn.net/Expert/topic/5296/5296764.xml?temp=.7666437>1 替换ntext数据类型字段create procedure p_replacentext(@vTable varchar(50), @vField varchar(30),@vBefore varchar(8000),@vAfter varchar(8000))asbegin Declare @Sql nvarchar(4000) declare @p_s varchar(800) Declare @p binary(16),@postion int,@rplen int set @Sql=N'Select @p=textptr('+@vField+'),@rplen=len('''+@vBefore+'''),@postion=patindex(''%'+@vBefore+'%'','+@vField+')-1 from '+@vTable exec sp_executesql @Sql,N'@p binary(16) output,@rplen int output,@postion int output ',@p output,@rplen output,@postion output while @postion >= 0 begin exec master.dbo.xp_varbintohexstr @p, @p_s out set @Sql=N'updatetext '+@vTable+'.'+@vField+' '+@p_s+' '+cast(@postion as varchar(8))+' '+cast(@rplen as varchar(4))+' '''+@vAfter+'''' print @Sql --测试用 exec(@Sql) set @Sql=N'select @postion=patindex(''%'+@vBefore+'%'','+@vField+')-1 from '+@vTable exec sp_executesql @Sql,N'@postion int output ',@postion output endend --测试create TABLE t(col1 char(1000), col2 ntext)INSERT t SELECT '', 'aa北京中华人民共和国北京天安门北京asdsdf'+REPLICATE('a', 8000)select * from texec p_replacentext 't','col2','北京','首都' select * from tdrop table t--结果aa北京中华人民共和国北京天安门北京asdsdfaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...aa首都中华人民共和国首都天安门首都asdsdfaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...--测试2create TABLE t(col1 char(1000), col2 ntext)INSERT t SELECT '', 'aa北京中华人民共和国北京天安门北京asdsdf'+REPLICATE('a', 8000)select * from texec p_replacentext 't','col2','aa','首都' select * from tdrop table t--结果aa北京中华人民共和国北京天安门北京asdsdfaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa首都北京中华人民共和国北京天安门北京asdsdf首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都--后台执行(所影响的行数为 1 行)(所影响的行数为 1 行)updatetext t.col2 0xFFFF0812000000003801000001000000 0 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 24 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 26 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 28 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 30 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 32 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 34 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 36 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 38 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 40 2 '首都'...updatetext t.col2 0xFFFF0812000000003801000001000000 7928 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7930 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7932 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7934 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7936 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7938 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7940 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7942 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7944 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7946 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7948 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7950 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7952 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7954 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7956 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7958 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7960 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7962 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7964 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7966 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7968 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7970 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7972 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7974 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7976 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7978 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7980 2 '首都'updatetext t.col2 0xFFFF0812000000003801000001000000 7982 2 '首都'(所影响的行数为 1 行) 求一个Sql语句 求相关SQL语句 between and 在不同数据库的区别? 求教SQL语句 Sos!在线等一个查询 行列转换,请大家帮帮忙 怎么把这个表分解? 检测数据库性能的工具~~知道的进来讨论吧 SQL Server 安装程序无法连接到数据库服务进行服务器配置 请问下远程数据库怎样备份到本地啊?? text类型字段只能写入16个字符 Text字段出现的问题。
>2
http://community.csdn.net/Expert/topic/5296/5296764.xml?temp=.7666437
>1 替换ntext数据类型字段
create procedure p_replacentext(@vTable varchar(50), @vField varchar(30),@vBefore varchar(8000),@vAfter varchar(8000))
as
begin
Declare @Sql nvarchar(4000)
declare @p_s varchar(800)
Declare @p binary(16),@postion int,@rplen int
set @Sql=N'Select @p=textptr('+@vField+'),@rplen=len('''+@vBefore+'''),@postion=patindex(''%'+@vBefore+'%'','+@vField+')-1 from '+@vTable
exec sp_executesql @Sql,N'@p binary(16) output,@rplen int output,@postion int output ',@p output,@rplen output,@postion output
while @postion >= 0
begin
exec master.dbo.xp_varbintohexstr @p, @p_s out
set @Sql=N'updatetext '+@vTable+'.'+@vField+' '+@p_s+' '+cast(@postion as varchar(8))+' '+cast(@rplen as varchar(4))+' '''+@vAfter+''''
print @Sql --测试用
exec(@Sql)
set @Sql=N'select @postion=patindex(''%'+@vBefore+'%'','+@vField+')-1 from '+@vTable
exec sp_executesql @Sql,N'@postion int output ',@postion output
end
end --测试
create TABLE t(col1 char(1000), col2 ntext)
INSERT t SELECT '', 'aa北京中华人民共和国北京天安门北京asdsdf'+REPLICATE('a', 8000)
select * from t
exec p_replacentext 't','col2','北京','首都'
select * from t
drop table t
--结果
aa北京中华人民共和国北京天安门北京asdsdfaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...aa首都中华人民共和国首都天安门首都asdsdfaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa...--测试2
create TABLE t(col1 char(1000), col2 ntext)
INSERT t SELECT '', 'aa北京中华人民共和国北京天安门北京asdsdf'+REPLICATE('a', 8000)
select * from t
exec p_replacentext 't','col2','aa','首都'
select * from t
drop table t--结果
aa北京中华人民共和国北京天安门北京asdsdfaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa首都北京中华人民共和国北京天安门北京asdsdf首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都首都
--后台执行(所影响的行数为 1 行)
(所影响的行数为 1 行)updatetext t.col2 0xFFFF0812000000003801000001000000 0 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 24 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 26 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 28 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 30 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 32 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 34 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 36 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 38 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 40 2 '首都'
.
.
.
updatetext t.col2 0xFFFF0812000000003801000001000000 7928 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7930 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7932 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7934 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7936 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7938 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7940 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7942 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7944 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7946 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7948 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7950 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7952 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7954 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7956 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7958 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7960 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7962 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7964 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7966 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7968 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7970 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7972 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7974 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7976 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7978 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7980 2 '首都'
updatetext t.col2 0xFFFF0812000000003801000001000000 7982 2 '首都'(所影响的行数为 1 行)