【Oracle SQL】明示的データ変換

Oracle SQLを触り始めた初心者です。社内の練習問題等で日付や文字列を数値に変換して計算する問題を多く見かけたので、データ変換について復習します。

明示的データ型変換

明示的なデータ変換は、変換関数を使用して行います。
変換関数は、値をあるデータ型から別のデータ型に変換します。一般に、関数名は「データ型TOデータ型」という命名規則に従います。

日付や文字、数値の変換で使用する関数は下記になります。

1. TO_CHAR関数
   数値や日付型のデータを文字列に変換するための関数
2. TO_NUMBER関数
   文字列を数値に変換する関数
3. TO_DATE関数
   文字列を日付に変換する関数。

TO_CHAR関数

Oracle SQLのTO_CHAR関数は、数値や日付型のデータを文字列に変換するための関数になります。

使用方法

TO_CHAR(日付or数値データ,'表示書式'）

• 日付or数値データでは、文字列に変換する日付（DATE型）または数値（NUMBER型）を指定します。
• 表示書式では、文字列の書式を「YYYY/MM/DD」のように指定します。

書式の例

• YYYY: 4桁の西暦年
• YY:西暦年の2桁
• RR:西暦年の2桁
• MM:月
• DD:日
• HH:12時間表記の時
• HH24:24時間表記の時
• MI:分
• SS:秒
• 9:数値。正の場合は先頭に空白が付き、負の場合はマイナス記号 ( - )が付く。
• 0:数値。正の場合は先頭に空白が付き、負の場合はマイナス記号 ( - )が付く。（桁数に足りない分は0で埋められる。）
• FM:書式の幅になるまで先頭を0又は末尾を空白で埋める。
• FX:書式モデルと厳密に一致していなければエラーを発生させる。

使用例

日付を文字列に変換

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') FROM DUAL;

数値を文字列に変換

SELECT TO_CHAR(1,'999') FROM DUAL;

TO_NUMBER関数

Oracle SQLのTO_NUMBER関数は、文字列を数値に変換するための関数になります。

使用方法

TO_CHAR(文字データ[,'表示書式']）

• 文字データでは、数値に変換する文字列を指定します。文字列の中に小数点や正負の符号が含まれていても良いです。
• 表示書式では、文字列の書式モデルを指定します。
  書式モデルのうち、下記に示す文字は特別な意味を持ちます。
  0：1桁の数字で先行ゼロ
  D：小数点の記号
  G：桁区切りの記号
  S：正の符号または負の符号を表す
  nls_param：各言語サポートパラメータを指定します。

使用例

文字列を数値に変換

SELECT TO_NUMBER('12') FROM DUAL;

書式を指定して文字列を数値に変換

SELECT TO_NUMBER('123,456', '999G999') FROM DUAL;

TO_DATE関数

Oracle SQLのTO_DATE関数は、文字列を日付型に変換するための関数になります。

使用方法

TO_CHAR(文字データ[,'表示書式']）

• 文字データでは、日付に変換する文字列を指定します。
• 表示書式では、文字列の書式モデルを指定します。
  書式モデルのうち、下記に示す文字は特別な意味を持ちます.
  YYYY：4桁の西暦年
  MM：月
  DD：日
  HH：24時間表記の時
  MI：分
  SS：秒
  nls_param：各言語サポートパラメータを指定します。

使用例

文字列を日付に変換

SELECT TO_DATE('2020-01-01', 'YYYY-MM-DD') FROM DUAL;

数値を日付に変換

SELECT TO_DATE(20200101, 'YYYYMMDD') FROM DUAL;

関数のネスト

OracleSQLでは直接変換できないデータ型もあります。その際は関数をネストさせ、順に変換させる必要があります。

使用例

ここでは、日付を数値に変換させる場合の使用例を参考に下記に記します。

日付を数値に変換

TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'))

手順として、まず日付を文字列に変換し、その後で文字列を数値に変換します。

1. 日付を文字列に変換：
   TO_CHAR関数を内側で使用し日付を文字列に変換します。
2. 文字列を数値に変換：
   次に、TO_NUMBER関数を外側で使用して文字列を数値に変換します。

上記例のTO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMMDD'))では現在の日付をYYYYMMDD形式の数値で返します。

SYSDATE

SYSDATEは、現在の日付と時刻を戻すSQL関数です。

変換が直接できない場合は、ネストさせ、順に変換する

参考

Oracle Database 10g 入門 SQL基礎Ⅰ

https://segakuin.com/oracle/function/to_char.html
https://www.shift-the-oracle.com/sql/functions/to_number.html