SQL Server : Nvarchar to Varchar

Question

I have a table with two columns, one is of type Varchar and the other in NVarchar.

I want to update all the rows so VarcharField = NVarcharField.

It won't let me because some of the rows contain chars that are not allowed in varchar column with the current code page.

How can I find these rows?

Is it possible to remove any char that doesn't fit the specific code page I'm using?

SQL Server 2012.

Answer 1

You can find the rows by attempting to convert the nvarchar() col to varchar():

select nvarcharcol
from t
where try_convert(varchar(max), nvarcharcol) is null;

Answer 2

Try this..

to find the rows with values that are not supported by varchar

declare @strText nvarchar(max)
set @strText = 'Keep calm and say தமிழன்டா'

select  cast(@strText as varchar(max)) col1 , N'Keep calm and say தமிழன்டா' col2

Here @strText has non-english chars, When you try to cast that into varchar the non-english chars turns into ????. So the col1 and col2 are not equal.

select nvar_col
from tabl_name
where nvar_col != cast(nvar_col as varchar(max))

Is it possible to remove any char that doesn't fit the specific code page I'm using?

update tabl_name 
    set nvar_col = replace(cast(nvar_col as varchar(max)),'?','')
where nvar_col != cast(nvar_col as varchar(max))

Replace ? with empty string and update them.