Japanese characters in VARCHAR column using Japanese_Unicode_CI_AS

Question

I am loading a table with the Japanese names from CSV file that are changing into question marks into SQL Table. The table that is storing these values is a varchar column. I understand that the varchar column is not Unicode and that that's the reason it is changing some of the characters to ??.

However, how come existing value written in Japanese is stored in varchar while ideally it should be in nvarchar?

Is there a way to convert nvarchcar to varchar?

The database is out of our control and we cannot change the schema.

Answer 1

As Tibor mentioned, the Japanese_Unicode_CI_AS collation (all Japanese_* collations, actually) can store Japanese characters in VARCHAR columns due to Windows code page 932 being a Double-Byte character set (DBCS). I believe there are just under 7800 Japanese characters mapped to the Windows-932 code page. However, Unicode contains more than 7800 Japanese characters.

It would be very helpful (maybe even necessary) to know some things in order to narrow down the problem:

1. Exactly how you are importing the CSV file. Are you using BCP or BULK INSERT / OPENROWSET(BULK...)?
2. An example of at least one Japanese name that is getting converted to ??
3. You say both that the "Japanese names from CSV file that are changing into question marks" and "it is changing some of the characters to ??", so:
   1. Are all Japanese characters changing into question marks, or only some?
   2. Are the Japanese characters changing into one question mark ( ? ), two ( ?? ), or some of each?

Without knowing the answers to those questions, I can say that there are two main possibilities:

1. (all Japanese characters imported as ?):   You aren't telling the import tool what the encoding of the CSV file is. Is it encoded as Windows-932 (or possibly Windows-31J)? Or is it a Unicode encoding such as UTF-8 or UTF-16 (which might be listed as "UCS-2" or "Unicode" depending on the tool)? If you are using BCP, you would need to use the command-line options of -c -C 932 for Windows-932, or -c -C 65001 for UTF-8. This problem should be fixable simply by setting the tool to use the correct code page.

2. (some Japanese characters imported as ? or ??):   If you are telling the import tool the correct encoding of the file, there are still Japanese characters that are not encoded in the Windows-932 code page. For example:

   -- DROP TABLE ##BCP;
   CREATE TABLE ##BCP ([Value] VARCHAR(50) COLLATE Japanese_Unicode_CI_AS);
   INSERT INTO ##BCP ([Value]) VALUES (N'ヤ :: ㋾ :: 🈂');
   SELECT * FROM ##BCP;
   

   returns:

   ヤ :: ? :: ??
   

   This problem can only be fixed by doing one of the following:

   1. Change the datatype of the column to be NVARCHAR (though you said that you can't change the schema)
   2. Change the collation of the column to use a UTF-8 collation (i.e. collation names ending in _UTF8; introduced in SQL Server 2019)

---

For info on working with collations / Unicode / encodings, please visit: Collations Info

Answer 2

I'm far from an expert when it comes to collations and Asian languages.

But my guess is that you have a Japanese collation (for instance Japanese_CI_AS) and since you aren't using nvarchar/Unicode, you end up with a double-byte character set (DBCS). Hence the presence of Japanese characters in your varchar column.

But when you load the data, your tool (whatever you use to load data) doesn't do the correct translation between the source and what is put in the database - hence your issue. I.e., you need to dig into the documentation for whatever tool you use to load the data and make sure that this tool can read and interpret the CSV file correctly and also interface to SQL Server correctly (considering you have a varchar which probably is Japanese/DBCS collation).

The "right" thing to do is of course to go Unicode/nvarchar, but as Paul mentioned in a comment, I assume that you meant "can't" where you said "can".

Additionally, I found a great elaboration from Solomon on this topic here: Storing Japanese characters in a table

Answer 3

How are you loading in your data? Is it going into an existing table in the database or creating a new table?

If it's an existing table that has column defined already as VARCHAR then there's not much you can do if you are unable to change the schema.

If it's creating a new table every time you load the data then this will depend on how you're importing the data. E.g. If you're using the Database Import Wizard or Flat File Import Wizard both provide a step where you can manually adjust the data types of the columns and you should be able to choose NVARCHAR for this column.

If you're scripting the import, then it'll depend on your SQL script but you should also be able to specify the column data type as NVARCHAR.