CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
user3550529
user3550529

Reputation: 135

Use REPLACE on column data type TEXT SQL Server

I try use this code to resolve acute accent on column type text:

UPDATE [AcuerdateWEB].[dbo].[Actividad]
SET Descripcion = REPLACE(CAST(Descripcion AS nvarchar(max)), 'ó', '&oacute')
WHERE Descripcion like '%&oacute%'

The problem is that the column's datatype is text and can not be replaced; but when I use the code above appears that the query was successfully and show the total of rows affected

Upvotes: 0

Views: 3370

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48836

Is it TEXT or NTEXT? You are mixing types in the REPLACE.

The reason it completes without error but does not do anything is that you are asking it to replace any ó with &oacute, and only in records that have &oacute.

The way this is currently written, it is ambiguous as to which one you want to replace with the other, so I will show both.

If you want to replace ó with &oacute:

UPDATE [AcuerdateWEB].[dbo].[Actividad]
SET Descripcion = REPLACE(CAST(Descripcion AS VARCHAR(MAX)), 'ó', '&oacute')
WHERE Descripcion like '%ó%';

If you want to replace &oacute with ó:

UPDATE [AcuerdateWEB].[dbo].[Actividad]
SET Descripcion = REPLACE(CAST(Descripcion AS VARCHAR(MAX)), '&oacute', 'ó')
WHERE Descripcion like '%&oacute%';

Also, if the data type is really NTEXT, just change the VARCHAR(MAX) to be NVARCHAR(MAX) and prefixed the 3 string literals with N, as in:

UPDATE [AcuerdateWEB].[dbo].[Actividad]
SET Descripcion = REPLACE(CAST(Descripcion AS NVARCHAR(MAX)), N'ó', N'&oacute')
WHERE Descripcion like N'%ó%';

Upvotes: 2

Related Questions