Get nvarchar records that were inserted as varchar

How can I get all values of an NVARCHAR column whose some of the values were accidently inserted without using the N prefix and so was replaced with ?, and then change those values into the correct form?

For example, if I have the following:

CREATE TABLE #Test
(
    Value nvarchar(max)
)

INSERT INTO #Test(Value) values(N'иытание1')
INSERT INTO #Test(Value) values('иытание2')
INSERT INTO #Test(Value) values(N'иытание3')

SELECT * FROM #Test

​x
 
CREATE TABLE #Test(    Value nvarchar(max))​INSERT INTO #Test(Value) values(N'иытание1')INSERT INTO #Test(Value) values('иытание2')INSERT INTO #Test(Value) values(N'иытание3')​SELECT * FROM #Test​

Output:

Value 
-------
иытание1
????????2 
иытание3

 
Value -------иытание1????????2 иытание3   ​

I want to get what was originally 'иытание2' and later turned into gibberish, and fix it.

Answer

You can’t, because it was converted to ? on insertion into the table. The original data is gone. A literal string is created as a VARCHAR unless you prefix it with N.

Advertisement

Answer