I am trying to remove/correct some special characters in my database that show strange behaviour when exported.

Specifically & 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):

FROM [Database].[dbo].[List]

UPDATE [Database].[dbo].[List]
SET  Loc1 = REPLACE(CAST(Loc1 as nVarchar(4000)), '[&]','and')
WHERE Loc1 LIKE '%[&]%';

I am using Microsoft SQL Server Management Studio.

What if I wanted to replace the apostrophe with the single quote (this last one doesn't cause issue when exported). e.g.

UPDATE [Database].[dbo].[List] 
SET Loc1 = REPLACE(CAST(Loc1 as NVARCHAR(4000)), CHAR(39), '''')
WHERE Loc1 LIKE'Lake'''s%';

It doesn't seem to be working, would the single quote need to be referenced in some way?

2 Answers 2


Do not enclose the literal in the REPLACE function in square brackets. The enclosures are not needed in the LIKE clause either since you are looking for a single character.


UPDATE [Database].[dbo].[List] 
SET Loc1 = REPLACE(CAST(Loc1 as nvarchar(4000)), N'&', N'and') 
WHERE Loc1 LIKE N'%&%';`

Also, I don't see a need for the CAST here:

UPDATE [Database].[dbo].[List] 
SET Loc1 = REPLACE(Loc1, N'&', N'and') 
WHERE Loc1 LIKE N'%&%';

You should be able to reference the individual characters you want to replace by character code instead, to ensure you're referencing the exact character. You can do so using the NCHAR() function.

Here's what your REPLACE() function turns into as an example: REPLACE(CAST(Loc1 as NVARCHAR(4000)), NCHAR(38), N'and')

The decimal code for an ampersand is 38, and for an apostrophe you can use 39.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.