Skip to Main Content
  • Questions

  • Converting mixed Latin + Japanese text to Shift-JIS

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Steve.

Asked: January 11, 2017 - 1:15 pm UTC

Last updated: January 14, 2017 - 7:47 am UTC

Version: 12c (12.1.0.2.0 64-bit)

Viewed 1000+ times

You Asked

Hi Tom,

I have a string of mixed Latin and Japanese characters that must be converted to Shift-JIS when output to a particular report (not always). The DB character set is AL32UTF8. When I execute this statement:
convert('H. pyloriの一次除菌における、[Product]/アモキシシリン/クラリスロマイシンの3剤併用療法による有効性及び安全性を検討する、[Product]/アモキシシリン/クラリスロマイシンの3剤併用療法を対照とした第3相多施設共同無作為化二重盲検並行群間比較試験', 'JA16SJISTILDE', 'AL32UTF8') as studydesc
from dual

It returns this:
STUDYDESC                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------
H. pylori�̈ꎟ���ۂɂ�����[Product]/�A���L�V�V����/�N�����X���}�C�V�����ܕ��p�Ö@�ɂ��L��y�ш�S���������[Product]/�A���L�V�V����/�N�����X���}�C�V�����ܕ��p�Ö@��ƂƂ���������{�݋�����������ӌ���s�Q�Ԕ�����


How can I peform this conversion successfully?

In case there's a question about the query tool it's SQL Developer 4.0.2.15, and this works fine:
select 'H. pyloriの一次除菌における、[Product]/アモキシシリン/クラリスロマイシンの3剤併用療法による有効性及び安全性を検討する、[Product]/アモキシシリン/クラリスロマイシンの3剤併用療法を対照とした第3相多施設共同無作為化二重盲検並行群間比較試験' as studydesc
from dual;

STUDYDESC                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------
H. pyloriの一次除菌における、[Product]/アモキシシリン/クラリスロマイシンの3剤併用療法による有効性及び安全性を検討する、[Product]/アモキシシリン/クラリスロマイシンの3剤併用療法を対照とした第3相多施設共同無作為化二重盲検並行群間比較試験

and Chris said...

I reached out to Sergiusz Wolicki, our NLS expert on this one. He had this to say:

"The CONVERT function should generally be used only for special repair tasks. Whenever any client/server conversion is involved, attempts to prepare data in some particular character set on the database side are usually unsuccessful."

And a question:

"Which particular mechanism do you use in SQL Developer to produce the report? I assume that they want to create an external file in the Shift-JIS (JA16SJISTILDE) character set. (It makes little sense to talk about character set for SQL Developer GUI.) If they want to save the Script Output pane content, they can go to Main Menu->Tools->Preferences...->Environment (tab) and set the Encoding to the desired one, e.g. "Shift_JIS". This will cause the output file to be saved in the desired encoding."

So it sounds like you need to set the encoding in your report to Shift-JIS to get the output you want.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Any suggestions for conversion within a query or function?

Steve Runyon, January 13, 2017 - 4:36 pm UTC

Thanks Chris & Sergiusz. To give a little more context, this is Oracle Argus executing a bunch of stored queries (literally stored query text, not procedures or functions) against the Oracle DB to generate an XML file (the report). So we don't have access to do charset conversation in a reporting tool or some such. If there's no way to do the conversion in a "vanilla" Oracle query or function I'll raise an Argus-specific SR to ask for suggestions.
Connor McDonald
January 14, 2017 - 7:47 am UTC

Let us know how you go
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.