引数として文字列を受け取りその結果を文字列や数値として返します。

・アルファベットの大文字・小文字を変換

UPPER関数、LOWER関数は、文字列内のアルファベットを大文字や小文字に変換する処理を行います。

INITCAP関数は、文字列の先頭を大文字に変換して、2文字目以降を小文字に変換する処理を行います。

・引数の文字列を操作

CONCAT関数は、引数で指定した2つの文字列を結合する処理を行います。

CONCAT( <文字列1>, <文字列2> )

SUBSTR関数は、引数で指定した文字列を指定した位置から指定した文字数文の文字を抜き出す処理を行います。


・<抜き出し位置>が正の場合は、先頭から数えて文字を抜き出し、負の場合は、末尾から数えて文字を抜き出します。

・<抜き出す文字数>は、省略可能です。省略した場合は、<抜き出し位置>から最後まで文字が抜き出されます。

SUBSTR( <文字列>, <抜き出し位置> [, <抜き出す文字数>] )

LPAD関数、RPAD関数は、引数で指定した文字を指定した文字列の長さになるまで文字を埋め込む処理を行います。


・LPAD関数は、文字列の左側に<埋め込み文字>を埋め込みます。

・RPAD関数は、文字列の右側に<埋め込み文字>を埋め込みます。

・<埋め込み文字>を指定しない場合は、空白の文字が埋め込まれます。

LPAD( <文字列>, <長さ> [, <埋め込み文字>] )

REPLACE関数は、引数で指定した文字列から検索文字列で検索を行い、置換文字列に置換する処理を行います。


・置換文字列を指定しない場合は、検索文字列を文字列から削除します。

REPLACE( <文字列>, <検索文字列> [, <置換文字列>] )

TRIM関数は、引数で指定した文字列の前後から連続した検索文字を削除する処理を行います。

また、指定するオプションにより動作が異なります。


・<検索文字>を省略した場合、<文字列>の前後から連続した空白文字を削除します。

・LEADINGを指定した場合、<文字列>の先頭から連続した<検索文字>を検索し削除します。

・TRAILINGを指定した場合、<文字列>の末尾から連続した<検索文字>を検索し削除します。

・BOTHを指定した場合（※1）、<文字列>の前後から連続した<検索文字>を検索し削除します。

※1 BOTHを指定しなくても同様の動作になります。

TRIM( [ LEADING | TRAILING | BOTH ] [ <検索文字> FROM ] <文字列> )

LENGTH関数は、引数で指定した文字列の文字数を数える処理を行います。

全角文字、半角文字関係なく文字数を返します。

LENGTH( <文字列> )

INSTR関数は、引数で指定した文字列から指定した文字が何文字目にあるか位置を返す処理を行います。


・<開始位置>や<出現回数>を省略した場合、デフォルトでは1が設定されます。

・<検索文字列>が見つからなかった場合、0を返します。

INSTR( <文字列>, <検索文字列> [, <開始位置> [, <出現回数>]] )

引数として数値を受け取りその結果を数値として返します。

・引数の数値を計算

ROUND関数は、引数で指定した数値から四捨五入を行い結果を返す処理を行います。

・<小数点桁数>を省略した場合、小数点以下の位が0として扱われその結果を返します。

・<小数点桁数>が負の場合、小数点の左側で四捨五入が行われます。

ROUND( <数値>, <小数点桁数> )

TRUNC関数は、引数で指定した数値から小数点以下を切り捨てる処理を行います。


・<小数点桁数>を省略した場合、小数点以下の位が0として扱われその結果を返します。

・<小数点桁数>が負の場合、小数点の左側で切り捨てが行われます。

TRUNC( <数値>, <小数点桁数> )

MOD関数は、引数で指定した数値を除算した結果の余りを返す処理を行います。

MOD( <数値1>, <数値2> )

POWER関数は、引数で指定した数値のべき乗した結果を返す処理を行います。

POWER( <数値1>, <数値2> )

・数値型や日付型を文字型に変換

TO_CHAR関数は、数値型や日付型のデータを文字型のデータに変換する処理を行います。

以下に、日時型から文字型に変換する処理の場合を記載します。

・<日時>を<フォーマット>に従って文字列に変換します。

・<フォーマット>を省略した場合、NLS_DATE_FORMATのパラメータに従って文字列に変換されます。

・NLS_DATE_LANGUAGEを指定すると、言語に応じた月名、曜日が使用されます。（※1）

※1 日本語を指定すると月曜日や火曜日が取得できます。英語を指定するとMondayやTuesdayが取得できます。

TO_CHAR( <日時> [, <フォーマット>] [, <表示言語>] )

以下に、数値型から文字型に変換する処理の場合を記載します。

・<フォーマット>を指定した場合、指定されたフォーマットに従って文字列に変換します。

・<フォーマット>の有効桁数が不足した場合、「#」で結果が表示されます。

例として、「12345」に対して<フォーマット>を「9999」とした場合、<フォーマット>の桁数が不足しているため、結果が「#」で表示されます。

TO_CHAR( <数値> [, <フォーマット>] )

・文字型を数値型に変換

TO_NUMBER関数は、文字型のデータを数値型のデータに変換する処理を行います。

・<文字列>を<フォーマット>に従って数値に変換します。

TO_CHAR( <文字列> [, <フォーマット>] )

・文字型を日付型に変換

TO_DATE関数は、文字型のデータを日付型のデータに変換する処理を行います。

・<文字列>を<フォーマット>に従って日時に変換します。

・<フォーマット>を省略した場合、NLS_DATE_FORMATのパラメータに従って文字列に変換されます。

・NLS_DATE_LANGUAGEを指定すると、言語に応じた月名、曜日が使用されます。（※2）

※2 日本語を指定すると月曜日や火曜日が取得できます。英語を指定するとMondayやTuesdayが取得できます。

TO_DATE( <文字列> [, <フォーマット>] [, <表示言語>] )

・文字型を数値型へ変換

数値型のカラムに文字列を設定する際に暗黙的データ型変換が行われます。

例として、お金という数値型のカラムに「1000」という文字列を設定する際にエラーが発生せずに設定することができます。

ただし、数値に変換できない文字リテラルの場合は、エラーが発生します。

・文字型を日付型に変換

数値型同様に日付型へも暗黙的データ型変換を行うことができます。

NLS_DATE_FORMATのパラメータに従って変換されます。

そのため、文字列がNLS_DATE_FORMATのパラメータに定義されたフォーマットと異なる場合は、エラーとなり型変換は行われません。

値がNULLか判断を行いその結果に応じた処理を行います。

NVL関数は、NULLの値を別の値に置き換えます。

・<文字列>がNULLの場合、<変換文字列>で指定した文字列の値を返します。

・<文字列>がNULL以外の場合、<文字列>の値が返されます。

・<文字列>と<変換文字列>のデータ型が異なる場合、<文字列>のデータ型に合わせて<変換文字列>のデータ型を暗黙的に変換します。

この時に、データ型の変換ができない場合、エラーが発生します。

NVL( <文字列> , <変換文字列> )

NVL2関数は、値がNULLかを判断してそれぞれに応じた値を返します。

・<文字列>と<変換文字列>のデータ型が異なる場合、<文字列>のデータ型に合わせて<変換文字列>のデータ型を暗黙的に変換します。

この時に、データ型の変換ができない場合、エラーが発生します。

NVL2( <文字列> , ,  )

NULLIF関数は、文字列とチェック値が等しい場合にNULLを返します。

・文字列にNULL値を設定することはできません。

・<文字列>と<チェック値>は同じデータ型である必要があります。

NULLIF( <文字列> , <チェック値> )

COALESCE関数は、複数の値の中から最初に見つかったNULLでない値を返します。

・全てNULLの場合、NULLを返します。

・全て同じデータ型である必要があります。

COALESCE( <文字列1>, <文字列2>, ..., <文字列n> )

・合計を求める

SUM関数は、複数レコードのカラム値を合計した値が返されます。

SELECT SUM(<列名>) FROM <テーブル名>;

・平均値、最大値、最小値を求める

AVG関数は、複数レコードのカラム値を平均した値が返されます。

MAX関数は、複数レコードのカラム値から最大の値が返されます。（※1）

MIN関数は、複数レコードのカラム値から最小の値が返されます。（※1）

※1 MAX関数やMIN関数は、数値以外にも日時型や文字型にも使用することができます。

SELECT AVG(<列名>), MAX(<列名>), MIN(<列名>) FROM <テーブル名>;

・件数を求める

COUNT関数は、レコード数をカウントしてその結果を返します。

引数に「＊（アスタリスク）」を指定した場合と「カラム名」を指定した場合で動作が異なります。

・COUNT(*)：対象のレコード数をカウントします。

・COUNT(カラム名)：対象のレコード数をカウントします。ただし、NULLのレコードはカウント対象外になります。

 

SELECT COUNT(*) FROM <テーブル名>;

主な集計ファンクションに記載した集計では、1つのグループとして集計することはできますが、種類ごとにグループ分けしてから集計することはできません。

そこで、GROU BY句を使用することでデータを種類ごとにグループ分けしてから集計できます。

SELECT <列名>, <集計ファンクション> FROM <テーブル名> GROUP BY <列名>;

例として、地域ごとに集計してお金の合計を取得する処理のイメージは以下の通りです。

・集計前に絞り込み

WHERE句によるデータの絞り込みを行った後にGROUP BY句でグループごとに集計が行えます。

SQLの実行順序の関係でGROUP BY句で実行する前にWHERE句の処理が行われます。

グループごとに集計する前に対象を絞り込む場合には、WHERE句を使って対象の絞り込みを行います。

・集計後に絞り込み

GROUP BY句でグループごとに集計した後に絞り込みを行う場合は、HAVING句を使用します。

SELECT <列名>, <集計ファンクション> FROM <テーブル名> 
GROUP BY <列名>
HAVING <条件>;

GROUP BY句によるグループ化処理を実行前に絞り込むのか、実行後に絞り込むのかでWHER句とHAVING句を使い分ける必要があります。

また、WHER句とHAVING句を組み合わせてデータを取得することもできるため、最終的にどのようなデータが必要なのかをイメージして使用する必要があります。