Search code examples
sqlsql-serverunicodenvarchar

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

Output:

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

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

Solution

  • 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.