SQL Server - Change information in a text field (REPLACE does not work)

See if it helps.

declare @MYTABELE table
(
  id int,
  orgsalkeyfil text 
)

insert into @MYTABELE values(1,'INFO:Alfa,Beta,Gama;CPPC-TM:0x3453;ALGOMAIS');

UPDATE @MYTABELE
SET orgsalkeyfil = CAST(REPLACE(CAST(orgsalkeyfil as NVarchar(4000)),'CPPC-TM', 'NEWINFO') AS NText)
WHERE orgsalkeyfil LIKE '%CPPC-TM%' 

select * from @MYTABELE

You can also specify and DATALENGTH(orgsalkeyfil) < 4000 if you have fields with very large data.

Reference 1. Reference 2.

Thank you @ Marconcelio.

Your code answered me. Among a 50k records, only 9 had the field with more than 4000 characters.

I've developed the code below that does not matter the size of the field, but it takes a lot of time to run / process.

DECLARE @ptrval binary(16);
DECLARE @insert_offset int;
DECLARE @id int;
SET @id = 1;
WHILE (select count(*) from torgsalkey where torgsalkey.orgsalkeyfil like N'%CPPC01PC1-LCM%') > 0
BEGIN
    if (select count(*) from torgsalkey where torgsalkey.orgsalkeyfil like N'%CPPC01PC1-LCM%' and orgsalkeyid = @id) > 0
    BEGIN
        SELECT @ptrval = TEXTPTR(orgsalkeyfil) FROM torgsalkey WHERE orgsalkeyid = @id
        SELECT
          @insert_offset = PATINDEX(N'%CPPC01PC1-LCM%', orgsalkeyfil) - 1
        FROM torgsalkey
        WHERE orgsalkeyid = @id

        WHILE @insert_offset > -1
        BEGIN
          UPDATETEXT torgsalkey.orgsalkeyfil @ptrval @insert_offset 13 'CPPC01';
          SELECT
            @insert_offset = PATINDEX(N'%CPPC01PC1-LCM%', orgsalkeyfil) - 1
          FROM torgsalkey
         WHERE orgsalkeyid = @id
         print @insert_offset
        END
    END
    SET @id = @id + 1;
END