It is not possible to recover the original value.
Character constants without the
N prefix to denote national characters are translated according to the database default collation code page. In cases where no clear mapping exists for the source character, the value is changed to a fallback character. This fallback may be similar but different character or the value
? when no alternate character exists.
You can observe this by casting the value as varbinary. Example with a
SQL_Latin1_General_CP1_CI_AS default database collation, which uses code page 1252:
DECLARE @n1 nvarchar(10) = 'Ḿấxiḿứś', @n2 nvarchar(10) = N'Ḿấxiḿứś';
@n1 AS corrupted_string
, CAST(@n1 AS varbinary(100)) AS corrupted_raw_value
, @n2 AS correct_string
, CAST(@n2 AS varbinary(100)) AS correct_raw_value;
Results of same script with an
Albanian_100_CI_AS database default collation shows the last character is correctly mapped due to the 1250 code page but characters changed to the '?' fallback are lost permanently like the previous example.