I am trying to remove/correct some special characters in my database that show strange behaviour when exported.
& and the apostrophe
I ran a query to find the values that contain the special characters, and while the query succeed at finding them, I still cannot replace/remove them with the second query UPDATE (nothing happens):
SELECT [Loc1],[IND_KEY] FROM [Database].[dbo].[List] WHERE CONVERT(VARCHAR(MAX), Loc1)LIKE'%[&]%'; UPDATE [Database].[dbo].[List] SET Loc1 = REPLACE(CAST(Loc1 as nVarchar(4000)), '[&]','and') WHERE Loc1 LIKE '%[&]%';
I am using Microsoft SQL Server Management Studio.