Shulou(Shulou.com)05/31 Report--

This article shows you how to replace ntext fields in sqlserver in batches. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

First, the problem description: 1. In Sql Server, the ntext/text/image field does not allow the application of the replace function to replace content; 2. With the convert field transformation, you can convert the ntext field to varchar (8000) and then replace it with the Relpace function, but this method does not work for ntext fields whose field length is greater than 8000.

Second, the general stored procedure is sorted out for problem solving. The code is as follows.

CREATE procedure [dbo]. [Proc_UpdateNTextField] @ TargetTable nvarchar (1000),-- target table name @ TargetField nvarchar (1000),-- target field name @ PKField nvarchar (1000),-- table primary key field name @ otxt nvarchar (1000) -- string @ ntxt nvarchar (1000) to be replaced-- string as begin declare @ SqlStr nvarchar (4000) set @ SqlStr = 'declare @ txtlen int' set @ SqlStr = @ SqlStr + 'set @ txtlen = len (''+ @ otxt +'') 'set @ SqlStr = @ SqlStr +' declare @ pos int 'set @ SqlStr = @ SqlStr +' set @ pos = 0 'set @ SqlStr = @ SqlStr +' declare curs cursor local fast_forward for select 'set @ SqlStr = @ SqlStr + @ PKField +' Textptr ('+ @ TargetField +') from'+ @ TargetTable + 'where' + @ TargetField + 'like''%'+ @ otxt +''set @ SqlStr = @ SqlStr +' declare @ ptr binary (16) 'set @ SqlStr = @ SqlStr +' declare @ id char (32) 'set @ SqlStr = @ SqlStr +' open curs' set @ SqlStr = @ SqlStr + 'fetch next from curs into @ id @ ptr 'set @ SqlStr = @ SqlStr +' while @ @ fetch_status = 0 'set @ SqlStr = @ SqlStr +' begin 'set @ SqlStr = @ SqlStr +' select @ pos= patindex ('%'+ @ otxt +'%') ProductDesc) from ProductTemp where ProductID=@id 'set @ SqlStr = @ SqlStr +' while @ pos > 0 'set @ SqlStr = @ SqlStr +' begin 'set @ SqlStr = @ SqlStr +' set @ pos=@pos-1 'set @ SqlStr = @ SqlStr +' updatetext'+ @ TargetTable +'.'+ @ TargetField +'@ ptr @ pos @ txtlen''+ @ ntxt + 'set @ SqlStr = @ SqlStr +' select @ pos= patindex (''%'+ @ otxt +'%' ProductDesc) from ProductTemp where ProductID=@id 'set @ SqlStr = @ SqlStr +' end 'set @ SqlStr = @ SqlStr +' fetch next from curs into @ id, @ ptr 'set @ SqlStr = @ SqlStr +' end 'set @ SqlStr = @ SqlStr +' close curs' set @ SqlStr = @ SqlStr + 'deallocate curs' EXECUTE sp_executesql @ SqlStr end

The above is how to replace ntext fields in sqlserver in batches. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.