Oracle SQL 関数その２(TO_CHAR,TO_DATE,TO

Oracle SQL 関数その２

このページはページの再構築に伴い移転しました。3秒後にジャンプします。
ジャンプしない場合は、以下のURLをクリックしてください。

TO_CHAR(日付)
日付を文字列へ変換
TO_CHAR(数値)
数値を文字列へ変換
TO_DATE
文字列の日付型へ変換
TO_NUMBER
文字列の数値への変換
TRIM
文字列空白除去
TRUNC(数値)
数値切捨て処理
TRUNC(日付)
日付切捨て処理
LPAD , RPAD
文字列の前後処理
CAST
データ型の変換

TO_CHAR(日付)　：　文字列へ変換

　stringRet := TO_CHAR( dateSrc [ , stringFormat [ , nlsParam ] ] );

　TO_CHARは日付データdateSrcを日付書式stringFormatに従ってVARCHAR2型のデータを返します。
  stringFormatを指定しない場合はデフォルトの日付書式に従います。
  nlsParamは月、日の名称及び略称の言語を指定します。

　通常の処理でよく使用する書式文字

日付書式文字	変換内容
D	曜日(1～7)
DD	日(1～31)
DDD	通年での日(1～366)
E	元号の略称
EE	元号
HH	時間(1～12)
HH24	時間(0～23)
MI	分(0～59)
MM	月(01～12)
YYYY	年
YY	年の下2桁

各書式文字の区切りとして"空白" "/" "-" "." "," ":" ";"の文字が許されています。

SQL> SELECT TO_CHAR(SYSDATE,'YYYY MM DD HH24 MI SS') FMSP,
  2         TO_CHAR(SYSDATE,'YYYY/MM/DD HH24/MI/SS') FMSL,
  3         TO_CHAR(SYSDATE,'YYYY.MM.DD HH24.MI.SS') FMDP,
  4         TO_CHAR(SYSDATE,'YYYY-MM-DD HH24-MI-SS') FMH,
  5         TO_CHAR(SYSDATE,'YYYY,MM,DD HH24,MI,SS') FMC,
  6         TO_CHAR(SYSDATE,'YYYY:MM:DD HH24:MI:SS') FMK,
  7         TO_CHAR(SYSDATE,'YYYY;MM;DD HH24;MI;SS') FMSK FROM DUAL;

FMSP                FMSL                FMDP                FMH                 FMC
------------------- ------------------- ------------------- ------------------- -------------------
FMK                 FMSK
------------------- -------------------
2002 11 17 15 35 59 2002/11/17 15/35/59 2002.11.17 15.35.59 2002-11-17 15-35-59 2002,11,17 15,35,59
2002:11:17 15:35:59 2002;11;17 15;35;59

ページのトップへ戻る

TO_CHAR(数値)　：　文字列へ変換

　stringRet := TO_CHAR( numberSrc [ , stringFormat [ , nlsParam ] ] );

　TO_CHARは数値データnumberSrcを日付書式stringFormatに従ってVARCHAR2型のデータを返します。
  stringFormatを指定しない場合はデフォルトの日付書式に従います。
  nlsParamは月、日の名称及び略称の言語を指定します。

　通常の処理でよく使用する書式文字

日付書式文字	変換内容
,(カンマ)	指定した位置にカンマを返す
.(ピリオド)	指定した位置に小数点を返す(書式の中で1個のみ指定)
0	先行0を返します
9	正の値の場合は先頭空白を埋め込み、負の場合には負の記号を先頭に埋め込んで指定の桁数にして返す
S	負の値は(-)を、正の値は(+)を前に付ける。(Sが先頭に在る場合)
FM	前後に空白を付けずに返す

SQL> SELECT ('''' || TO_CHAR(12345.6789,'999,999.999')    || '''') fm1,
  2         ('''' || TO_CHAR(12345.6789,'099,999.9999')   || '''') fm2,
  3         ('''' || TO_CHAR(12345.6789,'FM999,999.999')  || '''') fm3,
  4         ('''' || TO_CHAR(12345.6789,'FM099,999.9999') || '''') fm4,
  5         ('''' || TO_CHAR(-12345.6789,'999,999.999')   || '''') fm5,
  6         ('''' || TO_CHAR(-12345.6789,'099,999.9999')  || '''') fm6,
  7         ('''' || TO_CHAR(-12345.6789,'S999,999.999')  || '''') fm7,
  8         ('''' || TO_CHAR(-12345.6789,'S099,999.9999') || '''') fm8,
  9         ('''' || TO_CHAR(-12345.6789,'999,999.999S')  || '''') fm9,
 10         ('''' || TO_CHAR(-12345.6789,'099,999.9999S') || '''') fm10,
 11         ('''' || TO_CHAR(12345.6789,'$999,999.999')   || '''') fm11,
 12         ('''' || TO_CHAR(12345.6789,'$099,999.9999')  || '''') fm12
 13  FROM DUAL;

FM1            FM2             FM3            FM4             FM5            FM6
-------------- --------------- -------------- --------------- -------------- ---------------
FM7            FM8             FM9            FM10            FM11            FM12
-------------- --------------- -------------- --------------- --------------- ----------------
'  12,345.679' ' 012,345.6789' '12,345.679'   '012,345.6789'  ' -12,345.679' '-012,345.6789'
' -12,345.679' '-012,345.6789' ' 12,345.679-' '012,345.6789-' '  $12,345.679' ' $012,345.6789'

尚、数値が指定桁数より大きい場合には桁数分だけ"#"が返される為、指定桁数は十分な桁数が必要です。

SQL> SELECT TO_CHAR(123456789,'99,999,999') FMT8,
  2         TO_CHAR(123456789,'999,999,999') FMT9 FROM DUAL;

FMT8        FMT9
----------- ------------
###########  123,456,789

TO_DATE　：　日付型へ変換

　dateRet := TO_DATE( stringSrc [ , stringFormat [ , nlsParam ] ] );

　TO_DATEは文字列型データstringSrcを日付書式stringFormatに従ってDATE型のデータを返します。
  stringFormatを指定しない場合はデフォルトの日付書式に従います。
  nlsParamは月、日の名称及び略称の言語を指定します。

SQL> SELECT TO_DATE('2002/10/01 12:30:00','YYYY/MM/DD HH24:MI:SS'),
  2         TO_CHAR(TO_DATE('2002/10/01 12:30:00','YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS')
  3  FROM DUAL;

TO_DATE( TO_CHAR(TO_DATE('20
-------- -------------------
02-10-01 2002/10/01 12:30:00

TO_NUMBER　：　数値への変換

　numberRet := TO_NUMBER( stringSrc [ , stringFormat [ , nlsParam ] ] );

　TO_NUMBERは文字列型データstringSrcを日付書式stringFormatに従ってNUMBER型のデータを返します。
  stringFormatを指定しない場合はデフォルトの日付書式に従います。
  nlsParamは月、日の名称及び略称の言語を指定します。

stringFormat,nlsParamは通常使用したことがありません。

ページのトップへ戻る

TRIM　：　文字列空白除去

　stringRet := TRIM( [ [ LEADING | TRAILING | BOTH ] stringTrimChar FROM ]  stringSrc );

  LEADINGを指定 ：stringSrcの先頭からstringTrimCharの文字を削除されます。
  TRAILINGを指定：stringSrcの最後からstringTrimCharの文字を削除されます。
  BOTHを指定か指定無：stringSrcの先頭及び最後からstringTrimCharの文字を削除されます。
  
  stringTrimCharを指定しない場合はデフォルトで空白が削除されます。
  stringSrcのみ指定された場合は先頭及び最後から空白が削除されます。
  stringTrimChar, stringSrcのいづれかがNULLの場合はNULLを返します。
  (stringTrimChar：1個のみの文字の指定)

SQL> SELECT TRIM(LEADING  '0' FROM '00088899900') LEADING,
  2         TRIM(TRAILING '0' FROM '00088899900') TRAILING,
  3         TRIM(BOTH     '0' FROM '00088899900') BOTH,
  4         TRIM('0' FROM '00088899900')  BOTH2,
  5         TRIM('   8888999  ')          SPACE,
  6         TRIM(NULL FROM '00088899900') NULL1,
  7         TRIM(NULL)                    NULL2
  8  FROM DUAL;

LEADING  TRAILING  BOTH   BOTH2  SPACE   N N
-------- --------- ------ ------ ------- - -
88899900 000888999 888999 888999 8888999

TRIMに似た関数としてLTRIM,RTRIMがあります。

　stringRet := xTRIM( stringSrc [ , stringTrim ] );
  
  stringSrcの左側(右側)にあるstringTrimに設定された文字を削除します。
  stringSrcの左(右)から検索しstringTrimの文字以外が見つかった時点で処理をやめて文字列を返します。
  尚、stringTrimのデフォルト値は空白です。

SQL> SELECT LTRIM('0008889900','08') LT_1,LTRIM('0008889900','80') LT_2,LTRIM('  0008889900') LT_3,
  2         RTRIM('0008889900','09') RT_1,RTRIM('0008889900','90') RT_2,RTRIM('0008889900  ') RT_3
  3  FROM DUAL;

LT_1 LT_2 LT_3       RT_1   RT_2   RT_3
---- ---- ---------- ------ ------ ----------
9900 9900 0008889900 000888 000888 0008889900

ページのトップへ戻る

TRUNC(数値)　：　切捨て処理

　numberRet := TRUNC( numberSrc [ , numberUnit ] );

　TRUNCは数値データnumberSrcを小数点以下numberUnit桁までに切り捨てた数値を返します。
  numberUnitが省略されると０(ゼロ)と指定したことと同様で、小数点以下を切り捨てます。
  numberUnitが負の場合は、小数点から見てnumberUnit*(-1)の位置の桁以下を全て０にします。

numberUnitの値と切り捨てる桁についてはROUND関数と似ています。

numberUnit値	切り捨てる桁
3	小数点以下４桁目
2	小数点以下３桁目
1	小数点以下２桁目
0	小数点以下１桁目
-1	一の位の桁
-2	十の位の桁
-3	百の位の桁

SQL> SELECT TRUNC(1234.5678, 3) DP4 ,TRUNC(1234.5678, 2) DP3 ,
  2         TRUNC(1234.5678, 1) DP2 ,TRUNC(1234.5678, 0) DP1 ,
  3         TRUNC(1999.5678,-1) DPL1,TRUNC(1999.5678,-2) DPL2,TRUNC(1999.5678,-3) DPL3
  4  FROM DUAL;

       DP4        DP3        DP2        DP1       DPL1       DPL2       DPL3
---------- ---------- ---------- ---------- ---------- ---------- ----------
  1234.567    1234.56     1234.5       1234       1990       1900       1000

TRUNC(日付)　：　切捨て処理

　dateRet := TRUNC( dateSrc [ , dateFormat ] );

　TRUNCは日付データdateSrcを書式dateFormatに指定された単位まで近似した日付データを返します。
  dateFormatはROUND(日付)と同じものを用います。

dateFormat	丸め処理
YYYY,YEAR	年(7月1日で切り上げ)
Q	四半期
MONTH,MM	月(16日で切り上げ)
DD	日
DAY	週の開始日
HH,HH12,HH24	時
MI	分

SQL> SELECT TRUNC(TO_DATE('2002/06/30'),'YEAR') YEAR0630,
  2         TRUNC(TO_DATE('2002/07/01'),'YEAR') YEAR0701,
  3         TRUNC(TO_DATE('2002/06/30'),'Q') Q,
  4         TRUNC(TO_DATE('2002/06/15'),'MM')   MM0615  ,
  5         TRUNC(TO_DATE('2002/06/16'),'MM') MM0616,
  6         TRUNC(TO_DATE('2002/06/15 23:50:00','YYYY/MM/DD HH24:MI:SS'),'DD') DD,
  7         TO_CHAR(TRUNC(TO_DATE('2002/06/15 23:29:00','YYYY/MM/DD HH24:MI:SS'),'HH24')
  8                 'YYYY/MM/DD HH24:MI:SS') HH24
  9  FROM DUAL;

YEAR0630 YEAR0701 Q        MM0615   MM0616   DD       HH24
-------- -------- -------- -------- -------- -------- -------------------
02-01-01 02-01-01 02-04-01 02-06-01 02-06-01 02-06-15 2002/06/15 23:00:00

ページのトップへ戻る

LPAD , RPAD　：　文字列の前後処理

　strRet := LPAD( strSrc , n , strPad  );

　LPAD及びRPADは文字列データstrSrcを左側(右側に)連続してstrPadを埋めてn桁分の文字列を返します。

SQL> SELECT
  2   '<' || LPAD('ABC',10)              || '>' AS LPAD1
  3  ,'<' || LPAD('ABC',10,'*')          || '>' AS LPAD2
  4  ,'<' || LPAD('ABC',10,'*+')         || '>' AS LPAD3
  5  ,'<' || LPAD('ABC',10,'1234567890') || '>' AS LPAD4
  6  ,'<' || RPAD('ABC',10)              || '>' AS RPAD1
  7  ,'<' || RPAD('ABC',10,'*')          || '>' AS RPAD2
  8  ,'<' || RPAD('ABC',10,'*+')         || '>' AS RPAD3
  9  ,'<' || RPAD('ABC',10,'1234567890') || '>' AS RPAD4
 10  FROM DUAL;

LPAD1        LPAD2        LPAD3        LPAD4        RPAD1        RPAD2        RPAD3        RPAD4
------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------
<       ABC> <*******ABC> <*+*+*+*ABC> <1234567ABC> <ABC       > <ABC*******> <ABC*+*+*+*> <ABC1234567>

CAST　：　データの変換

　Ret := CAST( <expr> AS <データ型>  );

　CASTは<expr>で指定されたデータを<データ型>で指定されたデータ型に変換します。

以下に例を示します。最初のSQL文はNUMERIC型からCHAR,VARCHAR2型への変換ですが、CHAR型の場合は指定桁分の結果が返されるので、固定桁の表示がほしいときには利用できます。次のSQL文はDATE型からCHAR,VARCHAR2型への変換と、CHAR型からDATE型への変換です。'CHAR_TO_DATE'の結果はDATE型そのもを表示しても面白くないので、TO_CHAR関数で再びCHAR型へ書式付で変換しています。時刻がクリアされて変換されるところが注意するところです。

SQL> SELECT 1 AS NUM_TEST
  2        ,'#' || CAST(1 AS CHAR(10))     || '#' AS NUM_TO_CHAR
  3        ,'#' || CAST(1 AS VARCHAR2(10)) || '#' AS NUM_TO_VARCHAR2 FROM DUAL;

  NUM_TEST NUM_TO_CHAR  NUM_TO_VARCH
---------- ------------ ------------
         1 #1         # #1#

SQL> SELECT '#' || CAST(SYSDATE AS CHAR(20))     || '#' AS DATE_TO_CHAR
  2        ,'#' || CAST(SYSDATE AS VARCHAR2(20)) || '#' AS DATE_TO_VARCHAR2
  3        ,TO_CHAR(CAST('2005/01/22' AS DATE),'YYYY/MM/DD HH24:MI:SS') AS CHAR_TO_DATE FROM DUAL;

DATE_TO_CHAR           DATE_TO_VARCHAR2       CHAR_TO_DATE
---------------------- ---------------------- -------------------
#05-03-07            # #05-03-07#             2005/01/22 00:00:00