3

I have mistakenly stored unicode text into a varchar column in a table and now it is shown as question marks (???).

Is it possible to get our main information back from the column instead of question marks???

0

2 Answers 2

8

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.

1

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.

4
  • Thanks for your answer @clifton_h. I have backups but Unfortunately it cannot help because the mistake had happened from the beginning of the table design. Apr 29, 2019 at 1:50
  • Clifton (and @AsrarAhmadEhsan ): just to clarify: 1) "Unicode" is not 2 bytes, it's the UTF-16 encoding of Unicode that is 2 bytes per code unit, but might require 2 of those to map a code point (to handle supplementary characters). Hence, UTF-16 is either 2 or 4 bytes (and NVARCHAR is UTF-16). UTF-8 is 1 - 4 bytes, depending on the code point, and UTF-32 is always 4 bytes. VARCHAR holds various 8 bit encodings. Excluding the new ability to handle UTF-8 (an 8-bit encoding of Unicode), these code page-based encodings are either 1 or 2 bytes. (continued) Apr 29, 2019 at 14:05
  • 1
    2) The "?" one gets in VARCHAR when storing non-"?" characters is from the code page not supporting that character, not truncation specifically. Yes, 1 byte only has room for 256 values while 2 bytes has 65,536 values. So, fewer values to map to is, in a way, truncation, but it's not simply being chopped off. There are various code pages that handle different mappings of characters, they just can't support them all like Unicode does. However, there are 4 code pages for ` VARCHAR that can use 2 bytes to support more than 256 characters, but they still don't support all 65,536. Apr 29, 2019 at 14:12
  • Good points, @SolomonRutzky, I will adjust the answer. Thanks!
    – clifton_h
    Apr 29, 2019 at 14:21

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.