store unicode characters in sql varchar() fields
The '?' character replaced the original value because there was no equivalent character in the collation code page and is physically stored in the column instead of the original value.
Sorry to say but the original value is lost.
share improve this answer follow
answered Apr 27 '19 at 12:34
SHORT ANSWER: The correct answer is do you have backups before this change?
UNICODE IS A SPECIAL DATATYPE IN SQL SERVER
As stated in one answer, UNICODE is a universal coding scheme designed to work with any other “coding page” Microsoft DOCS - UNICODE SUPPORT. Think of Unicode as a large character mapping scheme that contains many non-standard characters of foreign languages.
SQL Server treats Unicode specially, with datatypes like NCHAR (fixed length), NVARCHAR (variable Unicode length) that will translate anywhere.
Additionally, and very importantly, UNICODE uses two character lengths compared to regular non-Unicode Characters. This is because that “map” has to be big enough to work with the special sizes of Unicode characters.
The storage size is two times n bytes + 2 bytes. nchar and NVARCHAR - Microsoft Docs This is twice the size of regular char/varchar and covers surrogate-pair key characters (not necessarily unique to Uni-code).
PROBLEM: VARCHAR HAS NO MATCH TO UNICODE BUT INSERTED ANYWAYS
Basically, the reason for “?” is because there is no matching equivalent for varchar was found. Unfortunately, SQL Server does not have a native way of preventing or warning you about these implicit conversions so the data is lost during insertion or modification.
SOLUTION: RESTORE OR RECREATE
Thus, you must restore or recreate the entries.
Was this a recent change? Restore to the latest backup before the change. Was this a design that has been around for a while? Then, see if the rows can be recreated. Otherwise, there is no hope of recovering data that was lost.