text怎么修改内容?如下
textid
{E0516352-3846-4AE6-AA13-60AA6206F09A}=0,{1C6DA9AA-D540-4234-A4D4-4F20FD179575}=11,{F51AE8CE-65F9-4FA8-B6F5-294601B35F92}=15
改为
textid
{E0516352-3846-4AE6-AA13-60AA6206F09A}=0,{1C6DA9AA-D540-4234-A4D4-4F20FD179575}=21,{F51AE8CE-65F9-4FA8-B6F5-294601B35F92}=15我想把
{1C6DA9AA-D540-4234-A4D4-4F20FD179575}等于后面的值都改为21
或者=11的都替换为=21
textid
{E0516352-3846-4AE6-AA13-60AA6206F09A}=0,{1C6DA9AA-D540-4234-A4D4-4F20FD179575}=11,{F51AE8CE-65F9-4FA8-B6F5-294601B35F92}=15
改为
textid
{E0516352-3846-4AE6-AA13-60AA6206F09A}=0,{1C6DA9AA-D540-4234-A4D4-4F20FD179575}=21,{F51AE8CE-65F9-4FA8-B6F5-294601B35F92}=15我想把
{1C6DA9AA-D540-4234-A4D4-4F20FD179575}等于后面的值都改为21
或者=11的都替换为=21
as
begin
Declare @Sql nvarchar(4000)
declare @p_s varchar(800)
Declare @p binary(16),@postion int,@rplen int
set @Sql=N 'Select @p=textptr( '+@vField+ '),@rplen=len( ' ' '+@vBefore+ ' ' '),@postion=patindex( ' '% '+@vBefore+ '% ' ', '+@vField+ ')-1 from '+@vTable
exec sp_executesql @Sql,N '@p binary(16) output,@rplen int output,@postion int output ',@p output,@rplen output,@postion output
while @postion > = 0
begin
exec master.dbo.xp_varbintohexstr @p, @p_s out
set @Sql=N 'updatetext '+@vTable+ '. '+@vField+ ' '+@p_s+ ' '+cast(@postion as varchar(8))+ ' '+cast(@rplen as varchar(4))+ ' ' ' '+@vAfter+ ' ' ' '
print @Sql
exec(@Sql)
set @Sql=N 'select @postion=patindex( ' '% '+@vBefore+ '% ' ', '+@vField+ ')-1 from '+@vTable
exec sp_executesql @Sql,N '@postion int output ',@postion output
end
end ---------
create TABLE t(col1 char(1000), col2 ntext)
INSERT t SELECT ' ', 'aa北京中华人民共和国北京天安门北京asdsdf '+REPLICATE( 'a ', 8000)
select * from t
exec p_replacentext 't ', 'col2 ', '北京 ', '首都 '
select * from t
drop table t
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([textid] [text])
INSERT INTO [tb]
SELECT '{E0516352-3846-4AE6-AA13-60AA6206F09A}=0,{1C6DA9AA-D540-4234-A4D4-4F20FD179575}=11,{F51AE8CE-65F9-4FA8-B6F5-294601B35F92}=15'-->SQL查询如下:
UPDATE tb SET
[textid]=replace(CAST([textid] AS VARCHAR(8000)),'=11,','=21,')SELECT * FROM [tb]
/*
textid
-------------------------------------------------------------------------------------------------------------------------------
{E0516352-3846-4AE6-AA13-60AA6206F09A}=0,{1C6DA9AA-D540-4234-A4D4-4F20FD179575}=21,{F51AE8CE-65F9-4FA8-B6F5-294601B35F92}=15(1 行受影响)*/