--image 和 Text 字段类似处理,也可以用 convert(varchar(8000),Image)把它转换成字符来处理替换。主题:text字段 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
直接 update Rs set photo='' where RsNo=1 就好了测试 Create table AA ( aa int, bb image )insert into AA select 1001,0x424D1E7B00000000000036000000280000008A000000390000000100200000000000E87A000000000000000000000000000000000000FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFUpdate AA Set bb='' Where aa=1001select * from AA
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
就好了测试
Create table AA
(
aa int,
bb image
)insert into AA
select 1001,0x424D1E7B00000000000036000000280000008A000000390000000100200000000000E87A000000000000000000000000000000000000FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFFFFF00FFUpdate AA
Set bb=''
Where aa=1001select * from AA