--更新text字段的值
create table PE_Soft(SoftIntro text,ChannelID integer)
insert into PE_Soft
select 'aaa ',1004
union all
select 'bbb ',1003
union all
select 'ccc ',1002 declare @ptr binary(16)
select @ptr=textptr(SoftIntro) from PE_Soft where ChannelID=1002
updatetext PE_Soft.SoftIntro @ptr null 0 'testing '
select * from PE_Soft drop table PE_Soft
--text字段增加处理--创建测试表
create table test(id varchar(3),detail text)
insert into test
select '001','A*B'--定义添加的的字符串
declare @s_str varchar(8000),@postion int
select @s_str='*C' --要添加的字符串
,@postion=null --追加的位置,null 加在尾部,0 加在首部,其他值则加在指定位置--字符串添加处理
declare @p varbinary(16)
select @p=textptr(detail) from test where id='001'
updatetext test.detail @p @postion 0 @s_str--显示处理结果
select * from test
go--删除测试表
drop table test
--text字段的替换处理--创建数据测试环境
create table test(id varchar(3),txt text)
insert into test
select '001','A*B'
go--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='*' --要替换的字符串
,@d_str='+' --替换成的字符串--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(txt)
,@rplen=len(@s_str)
,@postion=charindex(@s_str,txt)-1
from test where id='001'while @postion>0
begin
updatetext test.txt @p @postion @rplen @d_str
select @postion=charindex(@s_str,txt)-1 from test
end--显示结果
select * from testgo
--删除数据测试环境
drop table test--text字段的添加处理存储过程--全表--创建测试表
create table [user](uid int,UserLog text)
create table [order](uid int,state bit)insert into [user]
select 1,'a'
union all select 2,'b'
union all select 3,'c'insert into [order]
select 1,1
union all select 2,0
union all select 3,1
go--处理的存储过程
CREATE PROCEDURE spUpdateUserLog
@StrLog text,
@State int
AS
--定义游标,循环处理数据
declare @uid int
declare #tb cursor for select a.uid from [user] a join [order] b on a.uid=b.uid
where state=@stateopen #tb
fetch next from #tb into @uid
while @@fetch_status=0
begin
--字符串添加处理
declare @p varbinary(16)
select @p=textptr(UserLog) from [user] where uid=@uid
updatetext [user].UserLog @p null 0 @StrLog
fetch next from #tb into @uid
end
close #tb
deallocate #tb
go--调用示例:
exec spUpdateUserLog '123',1--显示处理结果
select * from [user]go--删除测试环境
drop table [user],[order]
drop proc spUpdateUserLog/*--测试结果uid UserLog
----------- ----------
1 a123
2 b
3 c123(所影响的行数为 3 行)
--*/
--text字段的替换处理--全表替换--创建数据测试环境
create table test(id varchar(3),txt text)
insert into test
select '001','A*B'
union all select '002','A*B-AA*BB'
go--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='*' --要替换的字符串
,@d_str='+' --替换成的字符串
--定义游标,循环处理数据
declare @id varchar(3)
declare #tb cursor for select id from test
open #tb
fetch next from #tb into @id
while @@fetch_status=0
begin
--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(txt)
,@rplen=len(@s_str)
,@postion=charindex(@s_str,txt)-1
from test where id=@id
while @postion>0
begin
updatetext test.txt @p @postion @rplen @d_str
select @postion=charindex(@s_str,txt)-1 from test where id=@id
end fetch next from #tb into @id
end
close #tb
deallocate #tb--显示结果
select * from testgo
--删除数据测试环境
drop table test************************
支持text字段处理的仅有:
下面的函数和语句可以与 ntext、text 或 image 数据一起使用。
函数 语句
DATALENGTH READTEXT
PATINDEX SET TEXTSIZE
SUBSTRING UPDATETEXT
TEXTPTR WRITETEXT
TEXTVALID
1:替换--创建数据测试环境
create table #tb(aa text)
insert into #tb select 'abc123abc123,asd'--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='123' --要替换的字符串
,@d_str='000' --替换成的字符串--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(aa),@rplen=len(@s_str),@postion=charindex(@s_str,aa)-1 from #tb
while @postion>0
begin
updatetext #tb.aa @p @postion @rplen @d_str
select @postion=charindex(@s_str,aa)-1 from #tb
end--显示结果
select * from #tb--删除数据测试环境
drop table #tb/****************全部替换************************/
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(aa) FROM #tb WHERE aa like '%数据2%'
if @ptrval is not null -- 一定要加上此句,否则若找不到数据下一句就会报错
UPDATETEXT #tb.aa @ptrval 0 null '数据3'
/****************在字段尾添加**********************************/
--定义添加的的字符串
declare @s_str varchar(8000)
select @s_str='*C' --要添加的字符串
--字符串添加处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(detail) from test where id='001'
updatetext test.detail @p null null @s_str
总结:
1:Text字段类型不能直接用replace函数来替换,必须用updatetext
2:字段比较不能用 where 字段 = ‘某数据’,可以用like来代替
3:updatetext时,若@ptrval值为空会出错,需注意
create table PE_Soft(SoftIntro text,ChannelID integer)
insert into PE_Soft
select 'aaa ',1004
union all
select 'bbb ',1003
union all
select 'ccc ',1002 declare @ptr binary(16)
select @ptr=textptr(SoftIntro) from PE_Soft where ChannelID=1002
updatetext PE_Soft.SoftIntro @ptr null 0 'testing '
select * from PE_Soft drop table PE_Soft
--text字段增加处理--创建测试表
create table test(id varchar(3),detail text)
insert into test
select '001','A*B'--定义添加的的字符串
declare @s_str varchar(8000),@postion int
select @s_str='*C' --要添加的字符串
,@postion=null --追加的位置,null 加在尾部,0 加在首部,其他值则加在指定位置--字符串添加处理
declare @p varbinary(16)
select @p=textptr(detail) from test where id='001'
updatetext test.detail @p @postion 0 @s_str--显示处理结果
select * from test
go--删除测试表
drop table test
--text字段的替换处理--创建数据测试环境
create table test(id varchar(3),txt text)
insert into test
select '001','A*B'
go--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='*' --要替换的字符串
,@d_str='+' --替换成的字符串--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(txt)
,@rplen=len(@s_str)
,@postion=charindex(@s_str,txt)-1
from test where id='001'while @postion>0
begin
updatetext test.txt @p @postion @rplen @d_str
select @postion=charindex(@s_str,txt)-1 from test
end--显示结果
select * from testgo
--删除数据测试环境
drop table test--text字段的添加处理存储过程--全表--创建测试表
create table [user](uid int,UserLog text)
create table [order](uid int,state bit)insert into [user]
select 1,'a'
union all select 2,'b'
union all select 3,'c'insert into [order]
select 1,1
union all select 2,0
union all select 3,1
go--处理的存储过程
CREATE PROCEDURE spUpdateUserLog
@StrLog text,
@State int
AS
--定义游标,循环处理数据
declare @uid int
declare #tb cursor for select a.uid from [user] a join [order] b on a.uid=b.uid
where state=@stateopen #tb
fetch next from #tb into @uid
while @@fetch_status=0
begin
--字符串添加处理
declare @p varbinary(16)
select @p=textptr(UserLog) from [user] where uid=@uid
updatetext [user].UserLog @p null 0 @StrLog
fetch next from #tb into @uid
end
close #tb
deallocate #tb
go--调用示例:
exec spUpdateUserLog '123',1--显示处理结果
select * from [user]go--删除测试环境
drop table [user],[order]
drop proc spUpdateUserLog/*--测试结果uid UserLog
----------- ----------
1 a123
2 b
3 c123(所影响的行数为 3 行)
--*/
--text字段的替换处理--全表替换--创建数据测试环境
create table test(id varchar(3),txt text)
insert into test
select '001','A*B'
union all select '002','A*B-AA*BB'
go--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='*' --要替换的字符串
,@d_str='+' --替换成的字符串
--定义游标,循环处理数据
declare @id varchar(3)
declare #tb cursor for select id from test
open #tb
fetch next from #tb into @id
while @@fetch_status=0
begin
--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(txt)
,@rplen=len(@s_str)
,@postion=charindex(@s_str,txt)-1
from test where id=@id
while @postion>0
begin
updatetext test.txt @p @postion @rplen @d_str
select @postion=charindex(@s_str,txt)-1 from test where id=@id
end fetch next from #tb into @id
end
close #tb
deallocate #tb--显示结果
select * from testgo
--删除数据测试环境
drop table test************************
支持text字段处理的仅有:
下面的函数和语句可以与 ntext、text 或 image 数据一起使用。
函数 语句
DATALENGTH READTEXT
PATINDEX SET TEXTSIZE
SUBSTRING UPDATETEXT
TEXTPTR WRITETEXT
TEXTVALID
1:替换--创建数据测试环境
create table #tb(aa text)
insert into #tb select 'abc123abc123,asd'--定义替换的字符串
declare @s_str varchar(8000),@d_str varchar(8000)
select @s_str='123' --要替换的字符串
,@d_str='000' --替换成的字符串--字符串替换处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(aa),@rplen=len(@s_str),@postion=charindex(@s_str,aa)-1 from #tb
while @postion>0
begin
updatetext #tb.aa @p @postion @rplen @d_str
select @postion=charindex(@s_str,aa)-1 from #tb
end--显示结果
select * from #tb--删除数据测试环境
drop table #tb/****************全部替换************************/
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(aa) FROM #tb WHERE aa like '%数据2%'
if @ptrval is not null -- 一定要加上此句,否则若找不到数据下一句就会报错
UPDATETEXT #tb.aa @ptrval 0 null '数据3'
/****************在字段尾添加**********************************/
--定义添加的的字符串
declare @s_str varchar(8000)
select @s_str='*C' --要添加的字符串
--字符串添加处理
declare @p varbinary(16),@postion int,@rplen int
select @p=textptr(detail) from test where id='001'
updatetext test.detail @p null null @s_str
总结:
1:Text字段类型不能直接用replace函数来替换,必须用updatetext
2:字段比较不能用 where 字段 = ‘某数据’,可以用like来代替
3:updatetext时,若@ptrval值为空会出错,需注意
不超過8000字節時用,set a=(select convert(varchar(8000),a)
csdn realgz
(http://topic.csdn.net/u/20080505/20/d2dffbbe-6d6b-41cf-b29a-41149540eafa.html?seed=562172452)if object_id('sp_replaceTextWithMultiColPk') is not null
drop proc sp_replaceTextWithMultiColPk
go create procedure sp_replaceTextWithMultiColPk
@tableName sysname,@colName sysname,@oldStr nvarchar(512),@newStr nvarchar(512),@whereStr nvarchar(200)=''
/*
为一个表内的text做统一替换的存储过程
缺点:慢,而且事实上还没办法支持任意表,而且代码看起来难受
因为 varchar的长度有限,当然还可以扩展,不过在sql 2005 里已经没这个问题了,懒得再写。
realgz 2008-05-05 */
as
begin
set nocount on
declare @cursor nvarchar(4000),@fetch nvarchar(4000),@insert nvarchar(4000),@where nvarchar(4000)
declare @replaceExec nvarchar(4000),@checkExec nvarchar(4000)
declare @rpPtr varbinary(16),@rpPostion int,@rpLen int
if object_id('tempdb..#key') is not null
drop table #key --获得主键列表
select sc.name keyName into #key from
sysobjects so
join sysindexes idx on so.parent_obj=idx.id
join sysindexkeys idk on so.parent_obj=idk.id and idx.indid = idk.indid
join syscolumns sc on so.parent_obj=sc.id and idk.colid=sc.colid
where so.xtype='PK' and so.parent_obj =object_id(@tableName) and idx.status & 0x800>0
if @@rowcount < 1
begin
raiserror ('表不符合要求或者没有这个表',12,1)
return
end
--增加键值列
alter table #key add keyValue sql_variant
alter table #key add primary key(keyName) --替换的长度
select @rpLen=len(@oldStr),@oldStr='%'+@oldStr+'%' --游标声明语句
select @cursor ='declare #c cursor static for select textptr('+@colname+'),PATINDEX ('''+replace(@oldStr,'''','''''')+''','+@colname+')-1'
select @cursor = @cursor +',['+keyName+']' from #key select @cursor = @cursor +' from ' +@tablename+' '+@whereStr --为游标的提取生成语句
select @fetch='',@insert='',@where=''
select @fetch = @fetch +'declare @'+keyName +' sql_variant '+char(13) from #key
select @fetch = @fetch +'fetch next from #c into @rpPtr,@rpPostion'
select @fetch = @fetch +',@'+keyName +char(13) from #key --把游标提取出来的值放到临时表的语句
select @insert = @insert +' insert into #key(keyName,keyValue ) '
select @insert = @insert + ' select '''+replace(keyName,'''','''''')+''',@'+keyName+' union all' +char(13) from #key
select @insert = left(@insert,len(@insert)-10)
select @fetch=@fetch +@insert
--每次获得游标行对应text指针的语句
select @where =' where 1=1'
select @where =@where +' and ['+keyName+']= (select keyValue from #key where keyName = '''+replace(keyName,'''','''''')+''')' from #key
--改写的sql
select @replaceExec = 'updatetext '+@tablename+'.'+@colname+' @rpPtr @rpPostion @rpLen @NewStr' --检查是否存在目标的sql
select @checkExec =' select @rpPostion = PATINDEX ('''+replace(@oldStr,'''','''''')+''','+@colname+')-1 from ' +@tablename+@where --声明游标
exec sp_executesql @cursor
truncate table #key
open #c --提取第一行
exec sp_executesql @fetch,N'@rpPtr varbinary(16) output,@rpPostion int output',@rpPtr output,@rpPostion output
--print @fetch
while @@fetch_status=0
begin
--只要@rpPostion>0 证明还需要替换
while @rpPostion>0
begin
--替换
exec sp_executesql @replaceExec,N' @rpPtr varbinary(16),@rpPostion int,@rpLen int,@newStr nvarchar(512)',@rpPtr,@rpPostion,@rpLen,@NewStr --重新判断是否需要替换
exec sp_executesql @checkExec,N'@rpPostion int output',@rpPostion output
end
truncate table #key --提取下一行
exec sp_executesql @fetch,N'@rpPtr varbinary(16) output,@rpPostion int output',@rpPtr output,@rpPostion output
end
close #c
deallocate #c
if object_id('tempdb..#key') is not null
drop table #key
set nocount off
end go
go
create table t_log(id int,t text)
gocreate trigger tr
on t
instead of insert
as
insert t_log select * from inserted
insert t select * from inserted
goinsert t select 1,'aa'
select * from t
select * from t_log
go
drop table t,t_log
go
下面是更新,当t中更新值时,同时更新 t_log
instead of update
as
declare @p binary(16)
declare @p1 ...
declare @p2 ...
select @p=textptr(t) from inserted
select @p1=textptr(b.t) from inserted a inner join t b on a.id=b.id
select @p2=textptr(b.t) from t a inner join t_log b on a.id=b.id
updatetext t.t @p1 0 null inserted.t @p
updatetext t_log.t @p2 0 null inserted.t @p
随手敲的,可能手误。
更新记录时候出现的错误提示
====author:fcuandy========
====date:2008.9.24========
========================*/--建立测试表 t,t_log
create table t(id int,t text)
go
create table t_log(id int,t text)
go
--插入测试数据. insert时的触发器我上面已经给你写过了,所以这里不再写了。
insert t select 1,'aa'
insert t_log select 1,'aa'go
--查看源数据
select * from t
/*
1 aa
*/
select * from t_log
/*
1 aa
*/
go--建立触器,当向 t 插入记录时,同时插入 t_log . 暂不支持多行。 若要支持多行,多用个临时表,以identity列循环操作
create trigger tr on t
instead of update
as
declare @p binary(16)
declare @p1 binary(16)
declare @p2 binary(16)
select * into # from inserted
select @p=textptr(t) from #
select @p1=textptr(b.t) from # a inner join t b on a.id=b.id
select @p2=textptr(b.t) from t a inner join t_log b on a.id=b.id
updatetext t.t @p1 0 null #.t @p
updatetext t_log.t @p2 0 null #.t @p
go
--更新t表t列为 'fcuandy'
update t set t='fcuandy'
--查看触发器执行结果
select * from t
/*
1 fcuandy
*/
select * from t_log
/*
1 fcuandy
*/
go
drop table t,t_log
go