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?

`N` in the prefix denotes that the subsequent string is in Unicode, check [this](https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements) — MJoy, Jul 31 '19 at 04:18

David Dubois · Accepted Answer · 2019-07-31T11:14:10.853

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'

Thank you. I have updated my question, since the table is exported from an outside CSV file and I take advantage of a stored procedure to do that, how should I modify the procedure to let it show Chinese characters? Or I just leave these messy codes and deal with them when I fetch these data. — Han, Aug 01 '19 at 05:48
This is really a whole new question. If you search on here for BULK INSERT and UNICODE you'll likely find an answer. Perhaps this will help: https://stackoverflow.com/questions/44965321/bulk-import-unicode-with-sql-server-2016 — David Dubois, Aug 01 '19 at 12:28

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

1 Answers1