とても便利なOracleの変換関数
引数の指定に多少に違いはあれど、多くの関数はOracle以外のDBでも提供されていることが多いのですが、 興味深いことに、次のデータ型変換関数は、意外と提供されていないんです（同等の機能を提供する他の関数はあります）。
| 関数 | 説明 |
|---|---|
| TO_CHAR | 数値型または日付型を文字型へ変換する |
| TO_DATE | 文字型を日付型に変換する |
| TO_NUMBER | 文字型を数値型に変換する |
関数そのものは、関数名が実態を表していますので簡単ですが、私たちにとって難しいのは 文化に基づく書式の指定です。 例えば、次の問題に挑戦してみてください。
問１
「2016年4月1日」という日付を「First of April,Twenty Sixteen」という形式で出力する必要があります。必要な結果を得るためには、どのSQL文を使用しますか。最適なものを選択しなさい。
-
a. SELECT TO_CHAR(TO_DATE('01-APR-16'),'fmDdspth “of” Month,Year')
FROM dual; -
b. SELECT TO_CHAR(TO_DATE('01-APR-16'),'fmddspth “of” month,year')
FROM dual; -
c. SELECT TO_CHAR(TO_DATE('01-APR-16'),'Ddspth “of” Month,Year')
FROM dual; -
d. SELECT TO_CHAR(TO_DATE('01-APR-16'),'fmDdsp “of” Month,Year')
FROM dual; -
e. SELECT TO_CHAR(TO_DATE('01-APR-16'),'fmDdth “of” Month,Year')
FROM dual;
「最適な」なので、正解は1つ、aです。各選択肢の違いは実行結果を比べるとわかりやすいでしょう。
なお、試験問題は英語で作成されたものが日本語化されています。しかし、「文化」までは日本語化していないので、日付や通貨は米国仕様だと考えてください。自分で試してみるときも、次のように言語や地域を米国に変えておきましょう。
SQL> --言語を変える SQL> alter session set NLS_LANGUAGE=american; Session altered. SQL> --地域を変える SQL> alter session set NLS_TERRITORY=america; Session altered.
まず、選択肢aとbの違いは、先頭が大文字か小文字かです。
SQL> --選択肢a
SQL> SELECT TO_CHAR(TO_DATE('01-APR-16'),'fmDdspth "of" Month,Year')
2 FROM dual;
TO_CHAR(TO_DATE('01-APR-16'),'FMDDSPTH"OF"MONTH,YEAR')
--------------------------------------------------------
First of April,Twenty Sixteen
SQL> --選択肢b
SQL> SELECT TO_CHAR(TO_DATE('01-APR-16'),'fmddspth "of" month,year')
2 FROM dual;
TO_CHAR(TO_DATE('01-APR-16'),'FMDDSPTH"OF"MONTH,YEAR')
--------------------------------------------------------
first of april,twenty sixteen
日にちを表すdd、月を表すmonth、年を表すyearの各先頭1文字が大文字か小文字かで、実行結果も大文字で表示されるか否かが異なります。設問は、先頭が大文字で表記されているので、小文字で出力される選択肢bは不正解です。
ちなみに、日本で4月を「卯月」といえる人のほうが少ないのではないかと思いますが、英語なら、日常的に「April」って言いますよね。書式mmは「04」と出力し、monthは「april」と出力します。MONは「APR」。年も同様yyyyは「2016」、yearは「twenty sixteen」です[1]。
次は選択肢cです。正解の選択肢aとは、書式にfmが指定されていないところが異なります。
SQL> --選択肢c
SQL> SELECT TO_CHAR(TO_DATE('01-APR-16'),'Ddspth "of" Month,Year')
2 FROM dual;
TO_CHAR(TO_DATE('01-APR-16'),'DDSPTH"OF"MONTH,YEAR')
--------------------------------------------------------
First of April ,Twenty Sixteen
fmは「前後に空白をつけずにかえす」書式なので、指定しないと「April」と「,」の間に空白が入り、設問と同じ出力ではなくなるため不正解です。
選択肢dは、日にちの書式にthが指定されていません。よって、「First」ではなく「One」と出力しているので不正解です。
thは、序数を指定する書式なんですが、日本語において日付を「順序／順番」という感覚で表現しないので、あまり意識しない書式なんじゃないかなと思います。でも、試験対策としては見逃さないでください。
SQL> --選択肢d
SQL> SELECT TO_CHAR(TO_DATE('01-APR-16'),'fmDdsp "of" Month,Year')
2 FROM dual;
TO_CHAR(TO_DATE('01-APR-16'),'FMDDSP"OF"MONTH,YEAR')
--------------------------------------------------------
One of April,Twenty Sixteen
日にちの書式はDAYとすると曜日が出力されるので、「One、Two、……」または「First、Second、……」とスペル出力したければ、spをつけます。それを指定していないのが選択肢e（不正解）です。これも日本語にはない感覚ですね[2]。
SQL> --選択肢e
SQL> SELECT TO_CHAR(TO_DATE('01-APR-16'),'fmDdth "of" Month,Year')
2 FROM dual;
TO_CHAR(TO_DATE('01-APR-16'),'FMDDTH"OF"MONTH,YEAR')
--------------------------------------------------------
1st of April,Twenty Sixteen
序数指定のth書式はあるので、「1st」と出力されます。
書式全体に対して、補足をしておきますね。
-
書式は「
'」（単一引用符）で全体を囲むこと -
書式内に文字（本設問の場合「of」）を含める場合は、「
"」（二重引用符）で必ず囲むこと -
ただし、「
/」（スラッシュ）、「.」（ピリオド）、「,」（カンマ）、「-」（ハイフン）は、記号のとおり結果に反映されるので、「"」で囲む必要なし
注
[1]: 試験には関係ないことを補足しておきますが、言語や地域を日本語にしてmonthとしても、「卯月」とは出力されません。「4月」と出力されます。
[2]: 再び試験には関係ない話ですが、言語／地域を「日本語／日本」に設定した状態でddspとしても、「one」って出力されます。
