I. Problem description:
1. In SQL Server, the ntext/text/image field cannot be replaced by the replace function;
2. By convert field conversion, you can convert the ntext field to varchar (8000) and replace it with the relpace function. However, this method is not applicable to ntext fields with a field length greater than 8000.
Ii. Problem Solving
Sort out general stored procedures,CodeAs follows:
<Br/> Create procedure [DBO]. [proc_updatentextfield] </P> <p> @ targettable nvarchar (1000), -- target table name <br/> @ targetfield nvarchar (1000 ), -- target field name <br/> @ pkfield nvarchar (1000), -- Name of the primary key field of the table <br/> @ otxt nvarchar (1000 ), -- string to be replaced <br/> @ ntxt nvarchar (1000) -- replaced string </P> <p> as <br/> begin </P> <p> declare @ sqlstr nvarchar (4000) </P> <p> set @ sqlstr = 'Clare @ txtlen int' <br/> set @ sqlstr = @ sqlstr + 'set @ txtlen = Len (''' + @ otxt + ''') '</P> <p> set @ sqlstr = @ sqlstr + 'Clare @ POS int' <br/> set @ sqlstr = @ sqlstr + 'set @ Pos = 0' </P> <p> set @ sqlstr = @ sqlstr + 'destare curs cursor local fast_forward for select' <br/> set @ sqlstr = @ sqlstr + @ pkfield + ', textptr ('+ @ targetfield + ') from '+ @ targettable + 'where' + @ targetfield +' Like ''% '+ @ otxt +' % ''' </P> <p> set @ sqlstr = @ sqlstr + 'Clare @ PTR binary (16) '<br/> set @ sqlstr = @ sqlstr +' declare @ ID char (32) '</P> <p> set @ sqlstr = @ sqlstr + 'open curs' </P> <p> set @ sqlstr = @ sqlstr + 'fetch next from curs @ ID, @ PTR '</P> <p> set @ sqlstr = @ sqlstr + 'while @ fetch_status = 0' <br/> set @ sqlstr = @ sqlstr + 'begin' <br/> set @ sqlstr = @ sqlstr + 'select @ Pos = patindex (''% '+ @ otxt +' % '', productdesc) from producttemp where productid = @ id' <br/> set @ sqlstr = @ sqlstr + 'while @ POS> 0' <br/> set @ sqlstr = @ sqlstr + 'begin' </P> <p> set @ sqlstr = @ sqlstr + 'set @ Pos = @ pos-1 '<br/> set @ sqlstr = @ sqlstr + 'updatetext' + @ targettable + '. '+ @ targetfield +' @ PTR @ POS @ txtlen ''' + @ ntxt + ''' <br/> set @ sqlstr = @ sqlstr + 'select @ Pos = patindex ('%' + @ otxt + '% '', productdesc) from producttemp where productid = @ id' </P> <p> set @ sqlstr = @ sqlstr + 'end' </P> <p> set @ sqlstr = @ sqlstr +' fetch next from curs into @ ID, @ PTR '</P> <p> set @ sqlstr = @ sqlstr + 'end' </P> <p> set @ sqlstr = @ sqlstr + 'close curs' <br /> set @ sqlstr = @ sqlstr + 'destlocate curs' </P> <p> execute sp_executesql @ sqlstr </P> <p> end