Cannot update varchar column with special characters

Question

Initially, I thought this issue was related to C # TransactionScope or Dapper.NET . But since I tested sql in SSMS and the problem remains, I assume this is a pure sql problem.

This is a (simplified) update that should update the varchar(40) column. I get no errors and the number of lines is 1. Old SparePartDescription is equal to EC801/¦USB/Exch Acc/JP/PE bag :

 declare @rowCount int UPDATE [tabSparePart] SET [SparePartDescription] = 'EC801/╡USB/Exch Acc/JP/PE bag' WHERE ([idSparePart] = 13912) set @rowCount = @@ROWCOUNT select @rowCount 

Thus, the only difference between these special characters: ╡ and ¦ .

Perhaps you have an idea why I cannot update this column.

Answer 1

Well, the special character you are using:

 ╡ 

Not supported in ASCII. Cm:

 DECLARE @x VARCHAR(32) = 'EC801/╡USB/Exch Acc/JP/PE bag'; SELECT @x; 

Result:

 EC801/¦USB/Exch Acc/JP/PE bag 

So, the update works fine, technically, it just doesn't do what you want, because in order to fit into the ASCII space, it must substitute your character for the real one.

To support this character, you need to use Unicode for your column (and maybe a specific sort, I'm not sure). This works great:

 DECLARE @x NVARCHAR(32) = N'EC801/╡USB/Exch Acc/JP/PE bag'; SELECT @x; 

Result:

 EC801/╡USB/Exch Acc/JP/PE bag 

It is important to specify the N prefix for string literals containing such characters ...