REPLACE function for SQL server is not changing the value [closed]

The column is of datatype NVARCHAR(MAX).

I want to add a string to my value

UPDATE Winner
SET [Text] = CAST(REPLACE(CAST([Text] AS NVARCHAR(MAX)), 'xxx You won ticket xxx', 'xxx You won semi final ticket xxx') AS NTEXT)
WHERE Id = 'xxxxxxx'

This doesn’t work.

Nothing is changing after the update – the value is unchanged.

How can I replace my text ?

Answer

You can use STUFF() :

DECLARE @UpdateText VARCHAR(255) = 'xxx You won ticket xxx' -- whatever data type you have
DECLARE @NewText VARCHAR(255) = 'xxx You won semi final ticket xxx' -- whatever data type you have

UPDATE Winner
     SET [Text] = STUFF([Text], PATINDEX('%' + @UpdateText + '%', [Text]), LEN(@UpdateText), @NewText)
WHERE Id = 'xxxxxxx'