ORACLEのTO_CHAR関数で日付と数値を文字列に変換する

覚えるべきキーワード
・よく使う日付書式
・TO_CHAR(日付,’日付書式’)
・TO_CHAR(日付,’日付書式’,nsl_date_language=’言語’)
・TH
・THSP
・TO_CHAR(数値,(数値書式’);
・よく使う数値書式
・9
・0
・$
・L

TO_CHAR関数ってORACLEでよく使いますよね。でも変換するために設定する書式ルールが細かいのでけっこう理解しづらいんですね。僕も苦労しました。当時、ネットで検索してもTO_CHARについて、詳しく書いてあるサイトがなかったので、TO_CHARで悩める者をなくすためにちょっと力を入れて書いてみました。
TO_CHARは日付を文字列に変換する、もしくは数値を文字列に変換する関数ですが、これをまとめて考えてしまうと、僕は頭がこんがらがったので、分けて説明しようと思います。

TO_CHARで日付を文字列に変換する

日付を文字列に変換するのは、日付データを○○/○○/○○や○○年○○月○○日のように文字列で表示したい時に使います。日付データはそのまま表示すると○○-○○-○○とハイフンで表示するため、画面には表示しづらいですね。日付を文字列で表示するには日付書式しっかり理解する必要があります。
この日付書式はなかなか曲者です。年度で言えば、YYかRRを指定すると1915年もしくは2015年と表示されます。月はMONかMONTHによって、FebかFeburaryと表示されます。書式で表示が変わるので、書式の理解はとても重要です。絶対理解してください。
日付の表示形式を決める書式はたくさんありますが、とりあえず良く使いそうな書式をまとめておきます。

(2018年11月13日16時30分25秒の時)

書式 説明
YYYY 西暦年4桁 2018
RRRR 西暦年4桁 2018
YY 西暦下2桁 18
RR 西暦下2桁 18
MM 月2桁 11
MON 短縮表記(3文字) NOV
MONTH 完全表記 NOVEMBER
DD 日2桁 13
AM AMかPMか表示 PM
HH24 24時間ベースで表示 16
HH 12時間ベースで表示 4
MI 30
SS 25
FM 空白を除去 ※空白を除去します

例も書きましたが、これはDBサーバの言語環境により異なります。日本語環境だと例えば、MONTHはFeburaryではなく、2月と表示されます。
曜日の書式もありますが、個人的にあまり使った記憶がないので省略します。調べたい時はググってください。
では早速、日付書式のサンプルです。

TO_CHARで日付を文字列に変換

(SYSDATEが2018年11月13日16時30分25秒の場合)

SELECT TO_CHAR(sysdate,(’YYYY/MM/DD AM HH:MI:SS’) AS “TIME” FROM dual;

sysdateはその時の時間を戻す関数なので、例えば2018年11月13日16時30分25秒だとしたら、このSQLを実行すると

TIME
2018/11/13 PM 4:30:25

と表示されます。/や:は日付書式含めてシングルクォテーションで囲めばそのまま表示されます。sysdateなのでdualテーブルを使っていますが、日付データの列名を指定したい場合はsysdateを対象の列名、dualを対象テーブル名に変更してください。そうすれば、その列に入っている日付データが同じ書式で表示されます。
次は年、月、日のように日本語verで表示したい時です。その時はダブルクォーテーションで囲めばそのまま表示できます。

TO_CHARで日付を文字列に変換(年、月、日)

(SYSDATEが2018年11月13日16時30分25秒の場合)


SELECT TO_CHAR(sysdate,(‘YYYY”年”MM”月”DD”日”’) AS “TIME” FROM dual;

このSQLを実行すると

TIME
2018年11月13日

こんな感じで表示ができます。

言語環境を変えて日付を文字列に変更することも可能です。

TO_CHARでNLSパラメータ指定

(SYSDATEが2018年11月13日16時30分25秒の場合)


SELECT TO_CHAR(sysdate,(MONTH, ‘nls_date_language = JAPANESE’) AS “TIME” FROM dual;

実行すると日本語環境で日付書式を表示します。

TIME
11月

ダブルクォーテーションで”月”と囲っていなくても、ベースを日本語環境に設定したので11月と表示します。

TH書式を設定すると表示形式を順序表示にします。

TO_CHARで日付を文字列に変換(TH指定)
(SYSDATEが2018年11月13日16時30分25秒の場合)


SELECT TO_CHAR(sysdate,(‘MM/DDth AM HH:SS’) AS “TIME” FROM dual;

TIME
11/13th PM 4:30:25

日にちの書式DDの後ろにthが付いているので、13thになります。

SP書式を設定すると対象がスペル形式で表示します。

TO_CHARで日付を文字列に変換(SP指定)

(SYSDATEが2018年11月13日16時30分25秒の場合)


SELECT TO_CHAR(sysdate,(‘MM/DDsp AM HH:SS’) AS “TIME” FROM dual;

TIME
11/thirteen PM 4:30:25

DDの後にSPが付いているので、日付がスペル表記になります。

ちなみにTHとSPは一緒に使えるので、DDTHSPのような書き方が可能です。この場合はDDSPTHでもOKです。

TO_CHARで数値を文字列に変換する

日付の文字列変換に比べると、数値の文字列変換はシンプルです。
数値書式も日付書式ほど複雑ではありません。よく使う数値書式として、僕は9、0、$、Lを理解しておけばよいかなと思っています。

書式 説明
9 数値1桁を表示する。先行ゼロは非表示
0 数値1桁を表示する。先行ゼロは表示
$ $を表示する
L 各国の通貨記号を表示する

数値書式は9と0の違いが一番重要だと思います。例文で比べてみましょう。

TO_CHARで数値を文字列に変換(先行ゼロ非表示)


SELECT TO_CHAR(0.11,’9.99’) AS “SUUJI” FROM dual;

数値書式には9を指定しています。9は先行ゼロが非表示書式なので、0.11は.11と表示します。

SUUJI
.11

一方で先頭に0を指定した場合です。

TO_CHARで数値を文字列に変換(先行ゼロ表示)


SELECT TO_CHAR(0.11,’0.99’) AS “SUUJI” FROM dual;

数値書式には先頭のみ0を指定しています。0は先行ゼロを表示するので、0.11は0.11と表示されます。

SUUJI
0.11

0か9かによって、先行ゼロの表示が変わっています。

$を指定した場合は数値の先頭に$マークがつきます。それだけと言えばそれだけです。

TO_CHARで数値を文字列に変換($を表示)


SELECT TO_CHAR(0.11,’$0.99’) AS “SUUJI” FROM dual;

SUUJI
$0.11

TO_CHAR関数は日付も数値も文字列に変換しますが、それぞれ書式が違うのでひとまとめにして考えるとごちゃごちゃになります。日付は日付、数値は数値としてTO_CHAR関数はおさえてください。
また、分かりやすく書いたつもりですが、説明不足な箇所があれば連絡ください。追記いたします。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です