SQL Command to insert Chinese Letters

Question

I have a database with one column of the type nvarchar. If I write

INSERT INTO table VALUES ("玄真") 

It shows ¿¿ in the table. What should I do?

I'm using SQL Developer.

Answer 1

Use single quotes, rather than double quotes, to create a text literal and for a NVARCHAR2/NCHAR text literal you need to prefix it with N

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( value NVARCHAR2(20) );

INSERT INTO table_name VALUES (N'玄真');

Query 1:

SELECT * FROM table_name

Results:

| VALUE |
|-------|
|    玄真 |

Answer 2

First, using NVARCHAR might not even be necessary.

The 'N' character data types are for storing data that doesn't 'fit' in the database's defined character set. There's an auxiliary character set defined as the NCHAR Character set. It's kind of a band aid - once you create a database it can be difficult to change its character set. Moral of this story - take great care in defining the Character Set when creating your database and do not just accept the defaults.

Here's a scenario (LiveSQL) where we're storing a Chinese string in both NVARCHAR and VARCHAR2.

CREATE TABLE SO_CHINESE ( value1 NVARCHAR2(20), value2 varchar2(20 char));
INSERT INTO SO_CHINESE VALUES (N'玄真', '我很高興谷歌翻譯。' )
select * from SO_CHINESE;

Note that both the character sets are in the Unicode family. Note also I told my VARCHAR2 string to hold 20 characters. That's because some characters may require up to 4 bytes to be stored. Using a definition of (20) would give you only room to store 5 of those characters.

Let's look at the same scenario using SQL Developer and my local database.

And to confirm the character sets:

SQL> clear screen
SQL> set echo on
SQL> set sqlformat ansiconsole
SQL> select * 
  2  from database_properties 
  3  where PROPERTY_NAME in 
  4     ('NLS_CHARACTERSET', 
  5      'NLS_NCHAR_CHARACTERSET');

PROPERTY_NAME            PROPERTY_VALUE   DESCRIPTION           
NLS_NCHAR_CHARACTERSET   AL16UTF16        NCHAR Character set   
NLS_CHARACTERSET         AL32UTF8         Character set         

Answer 3

First of all, you should to establish the Chinese character encoding on your Database, for example

UTF-8, Chinese_Hong_Kong_Stroke_90_BIN, Chinese_PRC_90_BIN, Chinese_Simplified_Pinyin_100_BIN ... 

I show you an example with SQL Server 2008 (Management Studio) that incorporates all of this Collations, however, you can find the same characters encodings in other Databases (MySQL, SQLite, MongoDB, MariaDB...).

1. Create Database with Chinese_PRC_90_BIN, but you can choose other Coallition:

   Select a Page (Left Header) Options > Collation > Choose the Collation

2. Create a Table with the same Collation:

3. Execute the Insert Statement

INSERT INTO ChineseTable VALUES ('玄真');