4

When I saved special characters in a column of type nvarchar, I did not use the prefix 'N', so the characters got saved as ??.

Is there any way to retrieve the original special characters from these question marks?

Any kind of suggestion will be appreciated.

4

Unfortunately no, if you have written special characters on a VARCHAR and not NVARCHAR (by omitting the N), their binary storage representation changed and there is no way to retrieve the original. Please note that this only applies when writting explicit strings as they need the N before the literal, if it's assigned throughout an update (for example) with a NVARCHAR column, the values will still retain their correct values.


You can see differences on the following examples. Here we write unicode chinese characters to an NCHAR and display both NCHAR and CHAR versions (their behaviour is similar to NVARCHAR and VARCHAR respectively, for this matter):

DECLARE @char NCHAR(1) = N'人'

SELECT
    NChar = @char,
    Char = CONVERT(CHAR(1), @char)

Result:

NChar   Char
人       ?

Here you can see the data loss, we show the binary representation of both the NCHAR and the CHAR with 2 different unicode characters. Please note that both are different characters but they are stored as the same on VARCHAR / CHAR data types.

DECLARE @char NCHAR(1) = N'人'
DECLARE @char2 NCHAR(1) = N'物'

SELECT
    NChar = @char,
    Char = CONVERT(CHAR(1), @char),
    NChar2 = @char2,
    Char2 = CONVERT(CHAR(1), @char2)

SELECT
    BinaryNChar = CONVERT(VARBINARY, @char),
    BinaryChar = CONVERT(VARBINARY, CONVERT(CHAR(1), @char)),
    BinaryNChar2 = CONVERT(VARBINARY, @char2),
    BinaryChar2 = CONVERT(VARBINARY, CONVERT(CHAR(1), @char2))

Results:

NChar   Char    NChar2  Char2
人       ?       物       ?

BinaryNChar BinaryChar  BinaryNChar2    BinaryChar2
0xBA4E      0x3F        0x6972          0x3F

NVARCHAR holds 2 bytes for each character while VARCHAR just 1. Considering this, you can't revert the conversion back from VARCHAR to NVARCHAR for special characters.

  • But what happens if you store a double-byte character (Unicode) of 1 character length in a char(2) field? Couldn't that be retrievable? – hot2use Aug 7 '18 at 8:36
  • Conversions are made on a character basis. Even if you have enough bytes on your data type to store the information, converting a unicode character (that requires 2 bytes per character) to a CHAR(n) (that requires just 1 per character) will only use it's 1-byte representation, leaving the rest of the bytes as blank. – EzLo Aug 7 '18 at 8:48
  • 2
    @hot2use, each character is converted regardless of the number of bytes used to represent the character. Non-ASCII characters are mapped according to the target collation code page. When the character doesn't exist in the code page, it is either mapped to a similar character or to '?' when no similar character exists. The data cannot be converted back to the original source character in these cases. – Dan Guzman Aug 7 '18 at 10:17
  • @DanGuzman yes, just found out the same during testing. Once you have an ? it's already broken. – hot2use Aug 7 '18 at 10:58

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.