Insert Japanese Language in to SQL

SQL Server supports multiple languages. Information about all the languages is stored in sys.syslanguages system view. You can run following script in Query Editor and see all the information about each language. Information about Months and Days varies for each language.

Syntax:

SELECT Alias, * FROM sys.syslanguages

The multiple languages are inserted only through the data types NCHAR, NVARCHAR and NTEXT, because the type of data is in different format. So the data should insert only in Unicode format.

Nchar (n) variables store fixed-length Unicode character strings consisting of exactly n characters (and, therefore, 2*n bytes). They are limited to 4,000 characters in size.

Nvarchar (n) variables store non-fixed length Unicode character strings consisting of approximately n characters. They consume 2*l+2 bytes of space, where l is the actual length of the string. They are limited to 4,000 characters in size.

Nvarchar (max) variables store non-fixed length Unicode character strings consisting of up to 536,870,912 characters. They consume l*2+2 bytes of space, where l is the actual length of the string.

Ntext variables store up to 2GB of text data (ANSI and Unicode, respectively), but cannot be used in many text operations. Therefore, they are usually only used to support legacy applications and have been replaced by the varchar (max) and nvarchar (max) data types.

Using Unicode characters in SQL Server:

Unicode is typically used in database applications which are designed to facilitate code pages which extend beyond the English and Western Europe code pages (Erland Sommarskog, a native of Sweden, refers to this set as "Germanic and Romance languages"), for example Japanese. Unicode is designed so that extended character sets can still "fit" into database columns. What this means is that Unicode character data types are limited to half the space, because each byte actually takes two bytes (Unicode is sometimes referred to as "double-wide"). There are many encoding schemes in the Unicode standard, but SQL Server only supports one: UTF-16.

“Unicode, the character-encoding scheme, is already a key standard for internationalization. Its relevance will increase as Web services and e-business becomes increasingly globalized.” “The world is a global village, trade crosses language barriers, and yet every one of us likes to feel comfortable within their own mother tongue. Unicode enabled us to give the local sense to every one of our users, while connecting the world of trade—which is the reason we will support Unicode in all of our products.”

An issue involved the storage of Japanese characters to the database. When we ran a query on the database using MS SQL Server (2008) Management Studio, the row that should have looked like this ‘日本の’ instead looked like question marks: ??????

Following are the solutions for avoid the above issue:-

1. The question marks indicate that the data was really not stored correctly (data loss).
2. If instead, your query results appear as square boxes (▯▯▯▯▯▯), your PC just does not have the language pack installed to view the characters. On Windows XP, go to Control Panel -> Regional and Language Options and check the Install files for East Asian languages checkbox.
3. One possible cause of the data turning to question marks is if you insert a literal Unicode string but do not use the N prefix (e.g., N‘myunicodestring’). Setting it as just ‘myunicodestring’ can cause non-Latin characters to go to question marks.
4. Another cause is if your column is not of a Unicode type. For instance, on SQL Server, you would want to use one of the n- character column types such as nvarchar(x).

Example

Here I’m using Japanese to create table name and column name and insert data in to the table. Please verify the table name, column name and data.

Insert Query

INSERT INTO 生産表(エントリ,項目,従業員キー,項目名,量,カテゴリ項目,ステータス,請求書,項目部,お問い合わせ先 ,項目のステータス,カテゴリ,部)VALUES(4,'CTB12',20988725,N'タービン圧縮',7,N'製造',N'いいえ',N'(形) 未決定の',N'空気力学',N'ジム',N'完了した',N'商用損害',N'宇宙航空事業部')

1. The table name is 生産表 [Production Table].But in SQL it shows in Unicode format, because windows XP is directly pointing to the Language English.When I change language in control pannel the table name and column name will shown in Japanese format. When I execute the query in text pane the query results will be in unicode format, and it is still in unicode format after importing the table into repository(OBIEE) .Once we use the column name in obiee reports we are able to see the data column in Japanese format.

2. The column name is 項目名 [Item name].Here I used the data type Nvarchar () because multi language can support only N prefix data types like NCHAR, NVARCHAR and NTEXT.

3. Insert Item name [タービン圧縮] in to the table. Here I need to initialize the N prefix before the Japanese character N’タービン圧縮’

4. Selection criteria. SELECT * FROM dbo.生産表.

5. The data also showing as Unicode format. Refer point 1

6. Column heading. Refer point 1