nText and String manipulation. SQL SERVER

Question

Parent question - Thanks to Paul White and Tom V for answer.

I am making little modification's to Paul White's answer to suit my requirements.

I would like to change the following to replace a string instead of inserting:

  CREATE TABLE dbo.PhilsTable
  (
  comment ntext NULL,
  anothercomment nvarchar(50) NULL
  );


 INSERT dbo.PhilsTable
 (comment, anothercomment)
 VALUES 
 (
 CONVERT(ntext, 
 N'ProdProd   1Prod Prod1   Prod Production'), 
 CONVERT(nvarchar(50), N'. This is inserted.')
 );

 DECLARE c 
 CURSOR GLOBAL 
 FORWARD_ONLY 
 DYNAMIC 
 SCROLL_LOCKS 
 TYPE_WARNING
 FOR
 SELECT
 TxtPtr = TEXTPTR(PT.comment),
 Src = N'Test',
 Offset = PATINDEX(N'%Prod%', PT.comment)
 FROM dbo.PhilsTable AS PT
 WHERE
 PT.comment LIKE N'%Prod%';

 OPEN c;

 DECLARE 
 @Ptr binary(16),
 @Src nvarchar(50),
 @Offset integer;

 SET STATISTICS XML OFF; -- No cursor fetch plans

 BEGIN TRANSACTION;

 WHILE 1 = 1
 BEGIN
 FETCH c INTO @Ptr, @Src, @Offset;

 IF @@FETCH_STATUS = -2 CONTINUE; -- row missing
 IF @@FETCH_STATUS = -1 BREAK; -- no more rows

 IF 1 = TEXTVALID('dbo.PhilsTable.comment', @Ptr)
     BEGIN

        -- Modify ntext value
        UPDATETEXT dbo.PhilsTable.comment @Ptr @Offset 0 @Src;
     END;
  END;

  COMMIT TRANSACTION;

  CLOSE c; DEALLOCATE c;

Column value:

ProdProd 1Prod Prod1 Prod Production

Expected result:

ProdProd 1Prod Prod1 Test Production

Current result:

PTestrodProd 1Prod Prod1 Prod Production

nText and String manipulation. SQL SERVER

0 Answers0