sql中利用 text更新,插入text类型的字段的方法带完整实例.doc

sql 中 更新text类型的字段-text字段增加处理-创建测试表create table test(id varchar(3),detail text)insert into testselect 001,A*B-定义添加的的字符串declare s_str varchar(8000),postion intselect s_str=*C -要添加的字符串 ,postion=null -追加的位置,null 加在尾部,0 加在首部,其他值则加在指定位置-字符串添加处理declare p varbinary(16)select p=textptr(detail) from test where id=001updatetext test.detail p postion 0 s_str-显示处理结果select * from testgo-删除测试表drop table test-text字段的替换处理-创建数据测试环境create table test(id varchar(3),txt text)insert into testselect 001,A*Bgo-定义替换的字符串declare s_str varchar(8000),d_str varchar(8000)select s_str=* -要替换的字符串 ,d_str=+ -替换成的字符串-字符串替换处理declare p varbinary(16),postion int,rplen intselect p=textptr(txt) ,rplen=len(s_str) ,postion=charindex(s_str,txt)-1from test where id=001while postion0begin updatetext test.txt p postion rplen d_str select postion=charindex(s_str,txt)-1 from testend-显示结果select * from testgo-删除数据测试环境drop table test-text字段的添加处理存储过程-全表-创建测试表create table user(uid int,UserLog text)create table order(uid int,state bit)insert into userselect 1,aunion all select 2,bunion all select 3,cinsert into orderselect 1,1union all select 2,0union all select 3,1go-处理的存储过程CREATE PROCEDURE spUpdateUserLogStrLog text,State intAS-定义游标,循环处理数据declare uid intdeclare #tb cursor for select a.uid from user a join order b on a.uid=b.uidwhere state=stateopen #tbfetch next from #tb into uidwhile fetch_status=0begin -字符串添加处理 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 uidendclose #tbdeallocate #tbgo-调用示例:exec spUpdateUserLog 123,1-显示处理结果select * from usergo-删除测试环境drop table user,orderdrop proc spUpdateUserLog/*-测试结果uid UserLog - -1 a1232 b3 c123（所影响的行数为 3 行）-*/-text字段的替换处理-全表替换-创建数据测试环境create table test(id varchar(3),txt text)insert into testselect 001,A*Bunion all select 002,A*B-AA*BBgo-定义替换的字符串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 testopen #tbfetch next from #tb into idwhile fetch_status=0begin -字符串替换处理 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 postion0 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 idendclose #tbdeallocate #tb-显示结果select * from testgo-删除数据测试环境drop table test*支持text字段处理的仅有:下面的函数和语句可以与 ntext、text 或 image 数据一起使用。函数 语句DATALENGTH READTEXTPATINDEX SET TEXTSIZESUBSTRING UPDATETEXTTEXTPTR WRITETEXTTEXTVALID1:替换-创建数据测试环境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 intselect p=textptr(aa),rplen=len(s_str),postion=charindex(s_str,aa)-1 from #tbwhile postion0beginupdatetext #tb.aa p postion rplen d_strselect postion=charindex(s_str,aa)-1 from #tbend-显示结果select * from #tb-删除数据测试环境drop table #tb/*全部替换*/DECLARE ptrval binary(16)SELECT ptrval = TEXTPTR(aa)FROM#tbWHERE 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 intselect p=textptr(deta