Convert to Chinese - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert to Chinese

Author

Topic

NewSQLMember
Starting Member

15 Posts

Posted - 2009-08-22 : 03:46:34

Hi,

Anyone can help me on how to convert these to chinese using select statement;

table : test
field : test
data type : nvarchar(100) as varbinary

Data
---
ae00c800b9004300b7007e00
a500e600b3007100b9004200bf00e900
c200e500c000f800b000b700b1006400a400ce00ac00fc00ae006500

Thanks in Advanced,
NSM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-22 : 03:51:43

This?

DECLARE	@Sample TABLE
	(
		Data VARBINARY(200)
	)

INSERT	@Sample
SELECT	0xae00c800b9004300b7007e00 UNION ALL
SELECT	0xa500e600b3007100b9004200bf00e900 UNION ALL
SELECT	0xc200e500c000f800b000b700b1006400a400ce00ac00fc00ae006500

SELECT	Data,
	CONVERT(NVARCHAR(100), Data) COLLATE Chinese_Traditional_Pinyin_100_CI_AS
FROM	@Sample

N 56°04'39.26"
E 12°55'05.63"

NewSQLMember
Starting Member

15 Posts

Posted - 2009-08-22 : 04:18:24

Hi Peso,

How can I resolve these error;

Incorrect syntax near 'Chinese_Traditional_Pinyin_100_CI_AS'.

even I change to Chinese_PRC_CI_AS;

Incorrect syntax near 'Chinese_PRC_CI_AS'.

Is there anything to changed with my sqlserver configurations.

Regards,
NSM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-22 : 04:44:42

Chinese_PRC_CI_AS is a valid collation code

are you using Microsoft SQL Server ?

if the collation is not valid you should get something like

Server: Msg 448, Level 16, State 1, Line 16
Invalid collation 'Chinese_Traditional_Pinyin_100_CI_AS'.

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-22 : 05:59:26

The "100" addition to the collation name is for SQL Server 2008.
Use same name without "100" and you should be fine.

N 56°04'39.26"
E 12°55'05.63"

NewSQLMember
Starting Member

15 Posts

Posted - 2009-08-22 : 06:56:04

Hi,

Im using Microsoft SQL Server 2005

Errors;

Msg 102, Level 15, State 1, Server SQLSERVER_PROD, Line 2
Incorrect syntax near 'Chinese_Traditional_Pinyin_100_CI_AS'.

Regards,
NSM

NewSQLMember
Starting Member

15 Posts

Posted - 2009-08-22 : 07:06:16

Hi,

Its works well now, when I try to run in SQL Server Editors, but it didnt work work with operating system. The way I like to execute in operating system I need the output t oa text file.

Last, question? how can I generate the output to a text file once I run in SQLServer Editor?

Regards,
NSM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-22 : 12:00:40

You can use BCP or SSIS to export data to a text file.
You can also use OPENROWSET.

N 56°04'39.26"
E 12°55'05.63"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2009-08-24 : 14:13:49

quote:
Originally posted by NewSQLMember

Data
---
ae00c800b9004300b7007e00
a500e600b3007100b9004200bf00e900
c200e500c000f800b000b700b1006400a400ce00ac00fc00ae006500

But hang on a second! There's clearly something a bit funny about that data: every other byte is 00! If you interpret that as UCS-2 (or UTF-16: it makes no odds here) then each character has a codepoint between 0 and 255, which certainly doesn't include any Chinese characters, as you can see from the result of Peter's (Peso's) SQL.

I think we're looking at multiple levels of mutilation here! In fact, I'm guessing that what you've got there is Big5 encoded Chinese where each byte has been padded out to 2 bytes with a nul. (I have no idea how such a thing would occur.) The SQL to get that back to nvarchar is a little more involved:


DECLARE	@Sample TABLE
	(
		Data VARBINARY(200)
	)

INSERT	@Sample
SELECT	0xae00c800b9004300b7007e00 UNION ALL
SELECT	0xa500e600b3007100b9004200bf00e900 UNION ALL
SELECT	0xc200e500c000f800b000b700b1006400a400ce00ac00fc00ae006500

SELECT	Data,
  CONVERT(nvarchar(100), ISNULL(( SELECT CONVERT(varchar(100), NULL) COLLATE Chinese_Taiwan_Stroke_CI_AS ),
    CONVERT(varbinary(100), CONVERT(varchar(100), CONVERT(nvarchar(100), Data)))))
FROM	@Sample

Since the output of that query looks like Traditional Chinese and Google translates them as "Tourism", "Transportation" and "Medical and health and beauty", it looks like I'm right!
(or Google's translation system just generates random business categories

)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources