0

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.

OfirD
  • 9,442
  • 5
  • 47
  • 90
  • 2
    You cannot. The `?` is not a placeholder; it's the actual replacement character used when the string was originally converted (to the database default collation, before it got stored in the column). You can see this more clearly with `SELECT CONVERT(VARBINARY(MAX), Value) FROM #Test`; the `3F00` are the codepoints for `?`. – Jeroen Mostert Aug 26 '21 at 09:14

2 Answers2

2

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.

Remy Lebeau
  • 555,201
  • 31
  • 458
  • 770
T Olaleye
  • 112
  • 4
-2

You can use below query to find out the rows, which have just ASCII characters.

Reference SO post on finding non-ascii characters

-- > 0 means NON-ASCII characters are present
-- = 0 means only ASCII characters are present
SELECT * FROM #TEST 
WHERE
patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,VALUE) = 0

????????2

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58