HOME > 関数編
関数
関数とはプログラム言語などでおなじみの関数（引数に値を渡すと処理した結果が取得できる）と同じです。SQL文の中に組み込んで使うことができます。
関数を使いこなすと、複雑なSQL文を書くことができます。
関数はなかなか奥が深いです。(^^
- 使用例（現在日付を「YYYY/MM/DD」型式で取得する）
- SELECT TO_CHAR(sysdate, 'YYYY/MM/DD') FROM DUAL;
主要関数一覧
| 変換関数 | |
|---|---|
| TO_CHAR(val[,fm]) |
valをCHAR型に変換する。 フォーマット形式を指定する場合はfmも指定する。 （フォーマット形式については→フォーマット形式編をご覧ください。） |
| TO_NUMBER(str) | strをNUMBER型に変換する |
| TO_DATE(str[,fm]) |
strをDATE型に変換する。 フォーマット形式を指定する場合はfmも指定する。 （フォーマット形式については→フォーマット形式編をご覧ください。） |
| TO_TIMESTAMP(str[,fm]) |
strをTIMESTAMP型に変換する フォーマット形式を指定する場合はfmも指定する。 （フォーマット形式については→フォーマット形式編をご覧ください。） |
| NVL(a,b) | aがnullならbを、nullでなければaを返す |
| NVL2(a,b,c) | aがnullならcを、nullでなければbを返す |
| CAST(m, n) | 値mをデータ型nに変換する （例）CAST(1, CHAR(1)) → '1' |
| 文字列操作関数 | |
|---|---|
| LENGTH(str) | 文字列の長さ（文字数）を返す （例）LENGTH('東京都') → 3 |
| LENGTHB(str) | 文字列の長さ（バイト数）を返す （例）LENGTHB('東京都') → 6 |
| LOWER(str) | 文字列を全て小文字にして返す （例）LOWER('aBcDe') → 'abcde' |
| UPPER(str) | 文字列を全て大文字にして返す （例）LOWER('aBcDe') → 'ABCDE' |
| SUBSTR(str,m[,n]) | strのm文字目からn文字分取り出す （nを省略するとstrのm文字目から最後まで取り出す） （例）SUBSTR('YOKOHAMA',5,3) → HAM、 SUBSTR('YOKOHAMA',5) → HAMA |
| SUBSTRB(str,m[,n]) | strのmバイト目からnバイト分取り出す （nを省略するとstrのmバイト目から最後まで取り出す） （例）SUBSTRB('YOKOHAMA',5,3) → HAM、 SUBSTRB('富士山',1,4) → '富士' SUBSTRB('富士山',1,5) → '富士 '（※2バイト文字の途中だったら 半角スペースになる） |
| TRIM(str) | strの左右の空白を無くす （例）TRIM(' ABC ') → 'ABC' |
| RTRIM(str) | strの右の空白を無くす （例）RTRIM(' ABC ') → ' ABC' |
| LTRIM(str) | strの左の空白を無くす （例）LTRIM(' ABC ') → 'ABC ' |
| INSTR(str1,str2[,m[,n]]) | str1のm文字目からstr2を検索してn番目に見つかった 文字位置を返すm,nを省略した場合は1とみなされる （例）INSTR('ABCDECD','CD',1,2) → 6 |
| DECODE(v1,v2,v3,v4) | v1 = v2 だったらv3を返し、違ったらv4を返す （例）DECODE(1, 1, '男', '女') → 男 |
| DECODE(v1,v2,v3,v4,v5,v6 ...) | v1 = v2 だったらv3を返し、v1 = v4 だったらv5を返し、 それ以外だったらv6を返す ※パラメータは255個まで （例）DECODE(2, 1, '赤', 2, '青', 3,'黄', '黒') → 青 |
| INITCAP(str) | 先頭の文字だけ大文字でその他は小文字に変換する （例）INITCAP('oracle') → Oracle |
| LPAD(str1, m, str2) | str1の左側にstr2を埋めてm文字で返す （例）LPAD('234',6,'0') → 000234 |
| RPAD(str1, m, str2) | str1の右側にstr2を埋めてm文字で返す （例）RPAD('234',6,'0') → 234000 |
| REPLACE(str1, str2, str3) | str1にあるstr2をstr3へ変換する （例）REPLACE('ABCABCABC','C','X') → ABXABXABX |
| TRANSLATE(str1, str2, str3) |
説明が難しいので例を示します。 例1：translate('ABCHIJ', 'ABCDEFGHIJ','1234567890') →123890 例2：translate('ACEGJ', 'ABCDEFGHIJ','1234567890') →13570 |
| ASCII(char) | 文字charに対応するASCIIコードを返す （例）ASCII('A') →65 |
| CHR(n) | ASCIIコードnに対応する文字を返す （例）CHR(65) →A （例）CHR(33440) →あ ※10進：33440＝16進：82A0 |
| TO_MULTI_BYTE(str) | strを2バイト文字に変換する （例）TO_MULTI_BYTE('1Aa') → １Ａａ |
| TO_SINGLE_BYTE(str) | strを1バイト文字に変換する （例）TO_SINGLE_BYTE('￥ａＡ') → \aA |
| GREATEST(str1[,strn..]) | 引数の中で一番大きい数を返す （例）GREATEST(1,4,6,3,32,9) → 32 |
| LEAST(str1[,strn..]) | 引数の中で一番小さい数を返す （例）LEAST(1,4,6,3,32,9) → 1 |
| REVERSE | 文字列を逆にする （例）REVERSE('ABCDE') → 'EDCBA' |
| 計算関数 | |
|---|---|
| MOD(m,n) | mをnで割った余りを返す （例）MOD(6,4) → 2 |
| TRUNC(m[,n]) | mを小数点以下n桁で切り捨てる （nを省略すると小数点以下全て切り捨てられる） （例）TRUNC(12.987,2) → 12.98、TRUNC(12.987) → 12 |
| ROUND(m[,n]) | 小数mを小数点以下n桁目で四捨五入する （例）ROUND(12.987,2) → 12.99、ROUND(1234.56,-2) → 1200 |
| CEIL(m) | 小数mを切り上げた整数を返す （例）CEIL(1.732) → 2、CEIL(-2.3) → -2 |
| FLOOR(m) | 小数mを切り下げた整数を返す （例）FLOOR(42.195) → 42、FLOOR(-42.195) → -43 |
| SIGN(m) | 数値mがマイナスなら-1、プラスなら1、ゼロなら0を返す （例）SIGN(-9) → -1、 SIGN(3) → 1、 SIGN(0) → 0 |
| ABS(m) | mの絶対値を返す （例）ABS(-8) → 8、ABS(8) → 8 |
| POWER(m, n) | mのn乗を返す （例）POWER(2,3) → 8 |
| SQRT(m) | mの平方根を返す （例）SQRT(3) → 1.73205081 |
| 集合関数 | |
|---|---|
| SUM(<カラム名>) | 指定したカラムの合計値を求める |
| COUNT(<カラム名>) | 指定したカラムの件数を求める |
| MAX(<カラム名>) | 指定したカラムの最大値を返す |
| MIN(<カラム名>) | 指定したカラムの最小値を返す |
| AVG(<カラム名>) | 指定したカラムの平均値を返す |
| 日付関数 | |
|---|---|
| ADD_MONTHS(d,m) | 日付dにmヶ月足した日付を返す （例）ADD_MONTHS(TO_DATE('05-11-8'),4) → 06-03-08 |
| LAST_DAY(d) | 日付dの月の最終日を返す （例）LAST_DAY(TO_DATE('05-11-8')) → 05-11-30 |
| NEXT_DAY(d,m) | 日付d以降で最も近い曜日mの日付を返す （曜日は1～7が日～土に対応） （例）NEXT_DAY(TO_DATE('06-03-03', 2)) → 06-03-06 |
| EXTRACT ([year|month|day|hour| minute|second] from d) |
TIMESTAMP型dの年月日時分秒のみを取得する ※年月日のみDATE型でもOK （例）EXTRACT(year from TO_DATE('2005/11/08')) → 2005 |
| MONTHS_BETWEEN(d1,d2) | 日付型d1と日付型d2の間の月数を返す （例）MONTHS_BETWEEN(TO_DATE('05-11-08'), TO_DATE('06-05-8')) → -6 |
| ROUND(d[,fm]) | 日付dを指定したフォーマットfmで四捨五入する （フォーマット形式については→フォーマット形式編をご覧ください。） （例）ROUND(TO_DATE('06-03-03'),'YY') → 06-01-01、 ROUND(TO_DATE('06-03-03'),'MM') → 06-03-01 |
| SYSDATE | 現在日時（年月日時分秒）を返す （例）SELECT SYSDATE FROM DUAL; |
| SYSTIMESTAMP | 現在日時（年月日時分秒ミリ秒）を返す （例）SELECT SYSTIMESTAMP FROM DUAL; |
| その他 | |
|---|---|
| USERENV('isdba') | 接続ユーザがDBAロールをもっているかどうかを返す （持っていればTRUE、無ければFALSE） |
| USERENV('language') | 使用している言語を返す （JAPANESE_JAPAN.JA16SJIS などが返る） |
| USERENV('sessionid') | セッション識別子を返す |
| USERENV('terminal') | 端末名を返す |
| USERENV('instance') | インスタンス識別番号を返す |
