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

Question

Hello guys. I have a table with a TEXT field. I need to change information within this text field only that I am not getting. The REPLACE function works in a varchar field, but does not work within the text field.

For example: I have a record where this field contains the following information: "INFO:Alfa,Beta,Gama;CPPC-TM:0x3453;ALGOMAIS" I need to change this value "CPPC-TM" to "NEWINFO". The final resite should be: "INFO:Alpha,Beta,Gamma;NEWINFO:0x3453;ALGOMAIS"

There is a way to do this via sql?

I’ve tried to:

update MYTABELE
set orgsalkeyfil = replace(orgsalkeyfil, 'CPPC-TM', 'NEWINFO');

But always returns an error.

If I run the same script in a varchar field it runs smoothly.

Answer 1

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 the and DATALENGTH(orgsalkeyfil) < 4000, if you have very large data fields.

Reference 1. Reference 2.

Answer 2

Thank you @Marconcílio.

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

I developed the code below that no matter the size of the field, but it takes a lot running/processing.

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