Oracle SQL 関数その2

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

移転先のページ





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が省略されると0(ゼロ)と指定したことと同様で、小数点以下を切り捨てます。
  numberUnitが負の場合は、小数点から見てnumberUnit*(-1)の位置の桁以下を全て0にします。

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

numberUnit値 切り捨てる桁
3 小数点以下4桁目
2 小数点以下3桁目
1 小数点以下2桁目
0 小数点以下1桁目
-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

ページのトップへ戻る