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