How can I tell if a column can be made into a varChar from a nVarChar?

Question

I'm looking for a way to determine if a column contains any non-ASCII data such that I can't convert it from nVarChar to varChar.

Ideally I would like to do this in T-SQL.

https://dba.stackexchange.com/questions/167489/detect-if-any-values-in-nvarchar-columns-are-actually-unicode — Hadi, Jan 21 '18 at 10:36
https://stackoverflow.com/questions/3891331/find-non-ascii-characters-in-varchar-columns-using-sql-server — Hadi, Jan 21 '18 at 10:36
"non-ASCII" seems overly specific. Are you going for a test that doesn't depend on the varChar collocation? If not, @Pரதீப் 's [answer](https://stackoverflow.com/a/48363762/2226988) would work for the database collation, allowing whichever non-ASCII characters it supports. (SQL Server doesn't have a collation with ASCII as the character set.) — Tom Blodget, Jan 21 '18 at 14:15
What I am really looking for is to answer the question "Does this column need to be nVarChar or could I change it to nVarChar without losing data?" — Jonathan Allen, Jan 21 '18 at 19:00
Possible duplicate of [Find non-ASCII characters in varchar columns using SQL Server](https://stackoverflow.com/questions/3891331/find-non-ascii-characters-in-varchar-columns-using-sql-server) — Vanity Slug - codidact.com, Feb 06 '18 at 17:55

score 8 · Answer 1 · edited Jan 21 '18 at 20:27

8

one simple way is by converting to varchar

select * 
from yourtable 
where nvarcharcolumn <> cast(nvarcharcolumn as varchar(8000))

edited Jan 21 '18 at 20:27

Martin Smith

438,706
87
741
845

answered Jan 21 '18 at 04:42

Pரதீப்

91,748
19
131
172

This isn't really reliable, see these problem characters that will get silently converted to close equivalents. http://rextester.com/HMFC61426 – Martin Smith Jan 21 '18 at 16:00
So you can add a `collate Latin1_General_BIN` to see if it round trips correctly as the varchar will already be implicitly cast back to nvarchar for the comparison. – Martin Smith Jan 21 '18 at 16:23
@MartinSmith, could show a example of using `collate Latin1_General_BIN`? – Jonathan Allen Jan 21 '18 at 19:02
1

`WHERE nvarcharcolumn <> cast(nvarcharcolumn as varchar(8000)) collate Latin1_General_BIN` – Martin Smith Jan 21 '18 at 19:04
@MartinSmith Never thought about that. Thanks Martin for opening my eyes yet again ;) – Pரதீப் Jan 22 '18 at 06:11

score 7 · Accepted Answer · answered Jan 21 '18 at 19:18

The other current answer is missing a step. All it checks is that after converting to varchar they still compare equal - not that they are actually the same character.

The string N'⁴＂＊８？' would silently be converted to '4"*8?' and still compare equal under many collations.

See

SELECT nvarcharcolumn,
       CAST(nvarcharcolumn AS VARCHAR(4000))
FROM   ( VALUES(N'⁴＂＊８？')) V(nvarcharcolumn )
WHERE  nvarcharcolumn <> cast(nvarcharcolumn AS VARCHAR(4000))

I would check that it round trips to the exact same value after casting to varchar and back again.You can do this with

WHERE nvarcharcolumn <> cast(nvarcharcolumn as varchar(4000)) collate Latin1_General_100_BIN

How can I tell if a column can be made into a varChar from a nVarChar?

2 Answers2