Update with adding prefix N before each cell to display Chinese characters

Question

I want to know how to use update command to display Chinese characters. Certainly I know I can realize this purpose when insertion, like:

create table sample1(val nvarchar(2))
insert into sample1 values(N'中文')

I know SQL Server use + to concat strings, so I have tried

Update table sample1
set column1 = 'N' + column1

But it just adds a N directly at the beginning of the following ???. I expected after the update, I can use query to display Chinese characters correctly.

Here is the table definition:

USE [dbo]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Scheme1].[sample](
    [Invoice_Info_ID] [int]  NOT NULL,
    [Order_Number] [int] NOT NULL,
    [Invoice_to_Address] [nvarchar](255) NULL,
    [Invoice_to_Contact_Name] [nvarchar](100) NULL,
    [Invoice_to_Contact_Phone] [int] NULL
) ON [PRIMARY]
GO

I want to update data for column [Invoice_to_Address] and [Invoice_to_Contact_Name]

Update

Based on the answer, I do modify the column attributes and I use a stored procedure to export a CSV file to SQL Server, this time, the data is shown as some messy code, like 合肥新普仪测科技有限公司. Below are the procedure defination:

USE [database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[extract_csv_into_table_with_same_cols] 
    @source NVARCHAR (500) = '',
    @destination NVARCHAR (200) = ''
AS 
    DECLARE @SQL NVARCHAR (MAX)

    SET @SQL = 'TRUNCATE TABLE ' + @destination

    EXEC (@SQL)

    SET @SQL = 'BULK INSERT ' + @destination + ' FROM ''' + @source + ''' WITH (FIRSTROW = 2, FIELDTERMINATOR = '','', ROWTERMINATOR = ''\n'', TABLOCK) '

    EXEC (@SQL)

    SET @SQL = 'SELECT * FROM ' + @destination

    EXEC (@SQL)

So how should I modify this procedure to show Chinese characters?

Answer 1

When SQL Server converts a string to varchar, any characters that it cannot store are replaced with question marks. The question marks you are seeing are actually stored in the database. The original data has been lost forever.

create table dbo.MyTable ( Data varchar(20) )
insert into MyTable ( Data ) values ( N'中文' )
select * from MyTable

The result is:

Data
??

Here you are seeing the actual text stored in the table. It is a string consisting of two question marks.

alter table MyTable alter column Data nvarchar(20)
select * from MyTable

The result is:

Data
??

The table is now capable of holding the Japanese text, but you still see question marks. Why? The original characters have been lost. These question marks are stored in the table.

insert into MyTable ( Data ) values ( N'日本人' )
select * from MyTable

The result is:

Data
??
日本人

This time, the Japanese text was stored in the table as expected. But the original text is gone forever.

---

In SQL, a string literal with an 'N' prefix is considered to be of type nvarchar. Think of it the same as prefixing a minus sign to a number. It changes the meaning of the literal constant.

 7  -- This has the value seven
-7  -- This has the value negative seven

 'Han' -- This is of type varchar
N'Han' -- This is of type nvarchar

declare @X int = 7
select '-' + @X     -- This does not return negative seven

declare @S nvarchar(20) = 'Han'
select 'N' + @S     -- This does not return N'Han'