Use REPLACE on column data type TEXT SQL Server

Question

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

Why are you still using TEXT? It's been deprecated for 10 years... — Aaron Bertrand
– Aaron Bertrand, Commented Jan 16, 2015 at 4:03

Solomon Rutzky · Accepted Answer · 2015-01-16 02:06:16Z

2

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'%ó%';

edited Jan 16, 2015 at 2:06

answered Jan 16, 2015 at 1:59

Solomon Rutzky

49.3k11 gold badges141 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3550529 Over a year ago

thank you, you're right,I set the strings in the wrong parameter.

Collectives™ on Stack Overflow

Use REPLACE on column data type TEXT SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related