[Oracle] VARCHAR2とNVARCHAR2を文字列結合して置換すると文字化けを起こす

データベース上のデータを加工してCSVファイルに出力するストアドプロシージャを作成・テストしていたら、REPLACE関数を使って文字列置換を行っている箇所で文字化けが発生しました。

どうもNVARCHAR2型のデータを置換すると文字化けを起こすようなのですが、文字化けするところとしないところがあります。

置換する前に'||'を使って文字列結合したりしているのも関係するかもしれません。

ということで、以下のような簡単なテストスクリプトを作成して調査してみました。

DECLARE
	wk_varchar VARCHAR2(1000) DEFAULT '"VARCHAR文字列"';
	wk_nvarchar NVARCHAR2(1000) DEFAULT '"NVARCHAR文字列"';
BEGIN

	DBMS_OUTPUT.PUT_LINE( '============================================================');
	DBMS_OUTPUT.PUT_LINE( '１．VARCHAR2＋NVARCHAR2');
	DBMS_OUTPUT.PUT_LINE( '------------------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE( '【文字列結合のみ】' );
	DBMS_OUTPUT.PUT_LINE( wk_varchar || wk_varchar );
	DBMS_OUTPUT.PUT_LINE( '【文字列結合＋置換】' );
	DBMS_OUTPUT.PUT_LINE( REPLACE( wk_varchar || wk_varchar, '"', '""' ) );
	DBMS_OUTPUT.PUT_LINE( '============================================================');

	DBMS_OUTPUT.PUT_LINE( '============================================================');
	DBMS_OUTPUT.PUT_LINE( '２．NVARCHAR2＋NVARCHAR2');
	DBMS_OUTPUT.PUT_LINE( '------------------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE( '【文字列結合のみ】' );
	DBMS_OUTPUT.PUT_LINE( wk_nvarchar || wk_nvarchar );
	DBMS_OUTPUT.PUT_LINE( '【文字列結合＋置換】' );
	DBMS_OUTPUT.PUT_LINE( REPLACE( wk_nvarchar || wk_nvarchar, '"', '""' ) );
	DBMS_OUTPUT.PUT_LINE( '============================================================');

	DBMS_OUTPUT.PUT_LINE( '============================================================');
	DBMS_OUTPUT.PUT_LINE( '３．NVARCHAR2＋VARCHAR2');
	DBMS_OUTPUT.PUT_LINE( '------------------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE( '【文字列結合のみ】' );
	DBMS_OUTPUT.PUT_LINE( wk_nvarchar || wk_varchar );
	DBMS_OUTPUT.PUT_LINE( '【文字列結合＋置換】' );
	DBMS_OUTPUT.PUT_LINE( REPLACE( wk_nvarchar || wk_varchar, '"', '""' ) );
	DBMS_OUTPUT.PUT_LINE( '============================================================');

	DBMS_OUTPUT.PUT_LINE( '============================================================');
	DBMS_OUTPUT.PUT_LINE( '４．VARCHAR2＋NVARCHAR2');
	DBMS_OUTPUT.PUT_LINE( '------------------------------------------------------------');
	DBMS_OUTPUT.PUT_LINE( '【文字列結合のみ】' );
	DBMS_OUTPUT.PUT_LINE( wk_varchar || wk_nvarchar );
	DBMS_OUTPUT.PUT_LINE( '【文字列結合＋置換】' );
	DBMS_OUTPUT.PUT_LINE( REPLACE( wk_varchar || wk_nvarchar, '"', '""' ) );
	DBMS_OUTPUT.PUT_LINE( '============================================================');

END;
/

VARCHAR2型とNVARCHAR2型の文字列変数を用意し、

　１．VARCHAR2型＋ VARCHAR2型
　２．NVARCHAR2型＋ NVARCHAR2型
　３．NVARCHAR2型＋ VARCHAR2型
　４．VARCHAR2型＋ NVARCHAR2型

の４パターンの文字列結合を行った場合と、各パターンの文字列結合後に置換を行った場合で文字化けが発生するかどうかを調べるというものですが、実行してみると、以下のような結果となりました。

※テストしたデータベースのデータベース・キャラクタセットはJASJISTILDE、各国語・キャラクタセットはAL16UTF16

============================================================
１．VARCHAR2＋NVARCHAR2
------------------------------------------------------------
【文字列結合のみ】
"VARCHAR文字列""VARCHAR文字列"
【文字列結合＋置換】
""VARCHAR文字列""""VARCHAR文字列""
============================================================
============================================================
２．NVARCHAR2＋NVARCHAR2
------------------------------------------------------------
【文字列結合のみ】
"NVARCHAR文字列""NVARCHAR文字列"
【文字列結合＋置換】
""NVARCHAR文字列""""NVARCHAR文字列""
============================================================
============================================================
３．NVARCHAR2＋VARCHAR2
------------------------------------------------------------
【文字列結合のみ】
"NVARCHAR文字列""VARCHAR文字列"
【文字列結合＋置換】
""NVARCHAR文字列""""VARCHAR文字列""
============================================================
============================================================
４．VARCHAR2＋NVARCHAR2
------------------------------------------------------------
【文字列結合のみ】
"VARCHAR文字列""NVARCHAR文字列"
【文字列結合＋置換】

"
"
V
A
R
C
H
A
ReⅧWR
"
"
"
"
N
V
A
R
C
H
A
ReⅧWR
"
"
============================================================

VARCHAR2型＋NVARCHAR2型を置換するパターンのみが文字化けを起こすようです。

REPLACE関数の仕様ではVARCHAR2型もNVARCHAR2型も受け付けるようなのですが．．．

この現象、Oracleは”仕様”と言うのでしょうか、”バグ”というのでしょうか？

まぁ、ともかくこういう現象が発生するということは、VARCHAR2型とNVARCHAR2型をくっつけて置換することは避けた方が良さそうです。

どうしてもそうしたいときは、以下のようにTO_CHAR関数を使って明示的にNVARCHAR2型の文字列をVARCHAR2型に変換してからくっつけて回避すればいいでしょう。

REPLACE( wk_varchar || TO_CHAR( wk_nvarchar ), '"', '""' )

Archive Redo Blog

DBエンジニアのあれこれ備忘録

[Oracle] VARCHAR2とNVARCHAR2を文字列結合して置換すると文字化けを起こす