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

1、sql中更新text类型的字段-text字段增加处理-创建测试表 create table test(id varchar(3),detail text) in sert into test select 001,A*B-定义添加的的字符串declare s_str varchar(8000),posti on intselect s_str=*C-要添加的字符串,postion=null-追加的位置,null加在尾部，0加在首部，其他值则加在指定位置-字符串添加处理 declare p varb in ary(16)select p=textptr(detail) from test whe

2、re id=001updatetext test.detail p posti on 0 s_str-显示处理结果select * from test go-删除测试表drop table test-text字段的替换处理-创建数据测试环境 create table test(id varchar(3),txt text) in sert into test select 001,A*B go-定义替换的字符串declare s_str varchar(8000),d_str varchar(8000) select s_str=*-要替换的字符串,d_str=+-替换成的字符串-字符串替换处

3、理declare p varb in ary(16),posti on in t,rple n int select p=textptr(txt),rple n=le n(s_str),posti on=chari ndex(s_str,txt)-1from test where id=001 while posti on0beginupdatetext test.txt p posti on rple n d_strselect posti on=chari ndex(s_str,txt)-1 from test end-显示结果select * from test go-删除数据测试环境d

4、rop table test-text字段的添加处理存储过程-全表-创建测试表 create table user(uid int,UserLog text)create table order(uid in t,state bit) in sert into userselect 1,aunion all select 2,bunion all select 3,c in sert into orderselect 1,1union all select 2,0union all select 3,1go-处理的存储过程CREATE PROCEDURE spUpdateUserLogStrL

5、og text ,State intAS-定义游标，循环处理数据declare uid intdeclare #tb cursor for select a.uid from user a join order b on a.uid=b.uid where state=stateope n #tbfetch n ext from #tb into uidwhile fetch_status=Obegin-字符串添加处理declare p varb in ary(16)select p=textptr(UserLog) from user where uid=uidupdatetext user

6、.UserLog p n ull 0 StrLogfetch n ext from #tb into uidendclose #tbdeallocate #tbgo-调用示例：exec spUpdateUserLog 123,1-显示处理结果select * from usergo-删除测试环境 drop table user,orderdrop proc spUpdateUserLog/*-测试结果uidUserLog1 a1232 b3 c123（所影响的行数为 3行）-*/-text字段的替换处理-全表替换-创建数据测试环境create table test(id varchar(3),

7、txt text) insert into testselect 001,A*Bunion all select OO2,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 testope n #tbfetch n ext from #tb into idwhile fetch_status=Ob

8、egin-字符串替换处理declare p varb in ary(16),posti on in t,rple n intselect p=textptr(txt),rple n=le n(s_str),posti on=chari ndex(s_str,txt)-1from test where id=idwhile posti on0beginupdatetext test.txt p posti on rple n d_strselect posti on=chari ndex(s_str,txt)-1 from test where id=idend fetch next from

9、#tb into idendclose #tbdeallocate #tb-显示结果select * from test go-删除数据测试环境drop table test*支持text字段处理的仅有：下面的函数和语句可以与ntext、text或image数据一起使用。函数语句DATALENGTHREADTEXTPATINDEXSET TEXTSIZESUBSTRINGUPDATETEXTTEXTPTRWRITETEXTTEXTVALID1潜换-创建数据测试环境create table #tb(aa text)in sert into #tb select abc123abc123,asd-

10、定义替换的字符串 declare s_str varchar(8000),d_str varchar(8000) select s_str=123- 要替换的字符串 ,d_str=000-替换成的字符串-字符串替换处理 declare p varb in ary(16),posti on in t,rple n intselect p=textptr(aa),rple n=le n(s_str),postio n=chari ndex(s_str,aa)-1 from #tb while posti on0beginupdatetext #tb.aa p posti on rple n d_s

11、trselect posti on=chari ndex(s_str,aa)-1 from #tbend-显示结果select * from #tb-删除数据测试环境sql中更新text类型的字段drop table #tb* _全立部替*sql中更新text类型的字段sql中更新text类型的字段DECLARE ptrval bi nary(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 varb in ary(16),posti on in t,rple n int select p=textpt