sql中利用 text更新,插入text類型的字段的方法帶完整實例.doc

sql 中 更新text類型的字段-text字段增加處理-創(chuàng)建測試表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-顯示處理結(jié)果select * from testgo-刪除測試表drop table test-text字段的替換處理-創(chuàng)建數(shù)據(jù)測試環(huán)境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-顯示結(jié)果select * from testgo-刪除數(shù)據(jù)測試環(huán)境drop table test-text字段的添加處理存儲過程-全表-創(chuàng)建測試表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-定義游標,循環(huán)處理數(shù)據(jù)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-調(diào)用示例:exec spUpdateUserLog 123,1-顯示處理結(jié)果select * from usergo-刪除測試環(huán)境drop table user,orderdrop proc spUpdateUserLog/*-測試結(jié)果uid UserLog - -1 a1232 b3 c123（所影響的行數(shù)為 3 行）-*/-text字段的替換處理-全表替換-創(chuàng)建數(shù)據(jù)測試環(huán)境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=+ -替換成的字符串-定義游標,循環(huán)處理數(shù)據(jù)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-顯示結(jié)果select * from testgo-刪除數(shù)據(jù)測試環(huán)境drop table test*支持text字段處理的僅有:下面的函數(shù)和語句可以與 ntext、text 或 image 數(shù)據(jù)一起使用。函數(shù) 語句DATALENGTH READTEXTPATINDEX SET TEXTSIZESUBSTRING UPDATETEXTTEXTPTR WRITETEXTTEXTVALID1:替換-創(chuàng)建數(shù)據(jù)測試環(huán)境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-顯示結(jié)果select * from #tb-刪除數(shù)據(jù)測試環(huán)境drop table #tb/*全部替換*/DECLARE ptrval binary(16)SELECT ptrval = TEXTPTR(aa)FROM#tbWHERE aa like %數(shù)據(jù)2%if ptrval is not null - 一定要加上此句，否則若找不到數(shù)據(jù)下一句就會報錯UPDATETEXT #tb.aa ptrval 0 null 數(shù)據(jù)3/*在字段尾添加*/-定義添加的的字符串declare s_str varchar(8000)select s_str=*C -要添加的字符串-字符串添加處理declare p varbinary(16),postion int,rplen intselect p=textptr(deta