Querying special character from SQL Server

Question

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.

Answer 1

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.