【Silver DBA】ファンクション

よく使われるファンクション

分類	ファンクション	内容
文字ファンクション	LOWER	小文字に変換
〃	UPPER	大文字に変換
〃	INITCAP	先頭文字を大文字、その他を小文字に変換
〃	CONCAT	連結
〃	SUBSTR	部分文字列の抜き出し
〃	LPAD	左側に文字を埋め込む
〃	RPAD	右側に文字を埋め込む
〃	REPLACE	置換
〃	TRIM	前後の文字を除去
〃	LENGTH	文字数
〃	INSTR	出現位置
数値ファンクション	ROUND	四捨五入
〃	TRUNC	切り捨て
〃	MOD	除算のあまり
〃	POWER	べき乗
日時ファンクション	ADD_MONTHS	数ヶ月後/前
〃	MONTHS_BETWEEN	2つの日時の期間を月単位で
〃	NEXT_DAY	次の特定の曜日の日付
〃	LAST_DAY	月末の日付
〃	SYSDATE	データベースサーバーのOSの日時
〃	ROUND	日時を四捨五入
〃	TRUNC	日時の切り捨て
変換ファンクション	TO_CHAR	文字列へ変換
〃	TO_NUMBER	数値へ変換
〃	TO_DATE	日時へ変換
NULL関連ファンクション	NVL	NULLを指定した値に置換
〃	NVL2	NULLかどうかに応じて異なる値
〃	NULLIF	値が等しいならNULL
〃	COALESCE	最初に見つかった非NULL値

使用例

LPAD, RPAD

//構文
LPAD/RPAD (文字列, 全体の文字数 [, 埋め込みたい文字列])
// 埋め込みたい文字列を省略した場合は、空白文字を埋め込む

//以下、例
SELECT LPAD('あいう', 10) FROM DUAL;
// → 　　　　　　　あいう

SELECT RPAD('あいう', 10, '*') FROM DUAL;
// →　あいう*******

TRIM

// 構文
TRIM (　[LEADING|TRAILING|BOTH] [削除したい文字 FROM]　文字列)

/*
削除したい文字を省略した場合、スペースが削除される
削除したい文字は、先頭/末尾から連続している文字
LEADINGは先頭から、TRAILINGは末尾から、BOTHは両方から検索する
デフォルトはBOTH
*/

//以下、例
SELECT TRIM(' あいう ') FROM DUAL;
// → あいう

SELECT TRIM('*' FROM 'あいうえお***') FROM DUAL;
// → あいうえお

SELECT TRIM(LEADING '*' FROM '***あいうえお***') FROM DUAL;
// → あいうえお***

INSTR

// 構文
INSTR (文字列, 検索文字列, [n番目 [, n回目] ])

/*
・文字列の中から検索文字列を探し、出現する位置を戻す
・「n番目」は、文字列のn番目から検索をし始めるという意味、デフォルトは1
・「n回目」はn回目の出現箇所の位置を戻すという意味、デフォルトは1
*/

//　以下、例
SELECT INSTR('あいうえおあいうえお', 'う') FROM dual;
// → 3

SELECT INSTR('あいうえおあいうえお', 'う', 4, 1) FROM dual;
// → 8

MONTHS_BETWEEN

// 構文
MONTHS_BETWEEN ('日付1', '日付2')
// 日付2から日付1までの間の月日を返す

// 以下、例
SELECT MONTHS_BETWEEN('2024/10/01 00:00:00', '2024/08/01 00:00:00')
// → 1

NVL

// 構文
NVL(カラム, 置換後の値)
// カラム列の値がNULLの場合、「置換後の値」に置換する

//以下、例
SELECT id, NVL(name, 'UNKNOWN') FROM TABLE_A WHERE sex='male';

ID    NAME
----  -------
1      TANAKA
2      YAMADA
3      UNKNOWN

NVL2

// 構文
NVL2(カラム, NULLでない場合の値, NULLの場合の値)

//以下、例
SELECT id, NVL2(name, 'VERIFIED', 'NOT VERIFIED') FROM  TABLE_A;

ID    NAME
----  -------
1      VERIFIED
2      VERIFIED
3      NOT VERIFIED

NULLIF

// 構文
NULLIF (カラム, 確認値)
// カラム列の値が「確認値」と等しい場合、NULLを返す
// 等しくない場合は、そのカラム列の値を返す

//以下、例
SELECT id, NULLIF(name, 'TANAKA') FROM TABLE_A WHERE name IS NOT NULL;
ID    NAME
----  -------
1      NULL
2      YAMADA
4      HAYASHI

COALESCE

// 構文
COALESCE(カラム1, カラム2, カラム3...)
// 列挙した値の中で、最初に見つかったNULLでない値を戻す

//以下、例
SELECT id, name, COALESCE(experience_year, studied_year, 0) "LEVEL" FROM employee;
ID    NAME     EXPERIENCE_YEAR  STUDIED_YEAR    LEVEL
----  -------  --------------   --------------  -------
1      TANAKA  10                               10
2      YAMADA                   5               5
3      NULL                                     0
4      HAYASHI 5                1               5