変換関数および条件式
本日もまた、SQL基礎の勉強。
今回は、変換関数と条件式の話。
変換関数には主に次のものがありますが、
数値を日付値に変換したり、日付値を数値に変換する関数はありません。
□-------------------------
・TO_CHAR：数値や日付値を文字列に変換する
・TO_DATE：文字列を日付値に変換する
・TO_NUMBER：文字列を数値に変換する
-------------------------------□
●NVL関数
NVL関数は値がNULL値であった場合に、別の値を返す関数です。
使用法は以下の通りです。
NVL('式1', '式2')
ただし、第2引数に指定する式は、
第1引数のデータ型と同じデータ型でなければなりません。
異なるデータ型を返す式を指定するとエラーとなります。
(暗黙的なデータ変換が行われる場合はエラーとなりません)
●NVL2関数
NVL2関数は値がNULL値か否かを調べ、
NULL値以外の場合は値A、NULL値の場合は値Bを返す関数です。
使用法は以下の通りです。
NVL2('式1', '式2', '式3')
第1引数の値がNULL値以外の場合、第2引数の値を返します。
第1引数の値がNULL値の場合、第3引数の値を返します。
ただし、第2引数と第3引数は同じデータ型でなければなりません。
異なるデータ型を返す式を指定するとエラーとなります。
(暗黙的なデータ変換が行われる場合はエラーとなりません)
●NULLIF関数
NULLIF関数は2つの値を比較して、等しい場合にNULLを返す関数です。
等しくない場合は第1引数の値を返します。
使用法は以下の通りです。
NULLIF('式1', '式2')
なお、第1引数にはリテラルのNULL値以外の値を指定しなければなりません。
第1引数にリテラルのNULL値は指定できませんが、
第1引数で指定した列の値などが結果としてNULL値となる場合は問題ありません。
<<例>>
X NULLIF(NULL, 999)
O NULLIF(aaa, 999) ※変数aaaにNULLが入る場合
第1引数が結果としてNULL値となる場合は、
第2引数がいかなる値であっても、NULL値が返されます。
(第2引数がNULL値であれば等しいのでNULL値、
第2引数がNULL値以外であれば等しくないので第1引数のNULL値が返される為）
●COALESCE関数
COALESCE関数は引数の値を判定し、
最初に見つかったNULL値以外の値を返す関数です。
使用法は以下の通りです。
COALESCE('式1', '式2' [, '式3' ...])
引数はすべて同じデータ型の値でなければなりません。
異なるデータ型の値を指定しても、
暗黙的なデータ変換は行われないためエラーとなります。
引数に指定された式全てがNULL値の場合、COALESCE関数はNULL値を返します。
●CASE文
CASE式には単純CASE式と検索CASE式の2種類があります。
検索CASE式は、条件を条件1から順番に判定し、
条件が真の場合に条件に対応する戻り値を返します。
真となる条件が1つも存在しない場合は、
ELSE句に指定されたデフォルトの戻り値が返されますが、
ELSE句が指定されていない場合はNULL値を返します。
CASE WHEN '条件1' THEN '戻り値1'
[WHEN '条件2' THEN '戻り値2' ...]
[ELSE 'デフォルトの戻り値']
END
CASE式では、WHEN句で比較条件やINやLIKEなどの演算子が使用できますが、
DECODE関数では使用できません。
CASE式は関数の引数として記述することもできますし、
デフォルトの戻り値にNULL値を指定することもできます。
●DECODE関数
DECODE関数は、第1引数に指定された式の値と、
第2引数以降に指定された条件を順に判定し、
値が合致した条件に対応する戻り値を返します。
DECODE('式', '条件1', '戻り値1'
[, '条件2', '戻り値2' ...]
[, 'デフォルトの戻り値'])
なお、DECODE関数の条件に、
WHERE句の条件に指定するような比較演算子を使用した
条件を記述することはできません。
また、条件が複数指定された場合の戻り値のデータ型は、
第3引数で指定された戻り値のデータ型が採用されます。
したがってそれぞれの戻り値は、第3引数の戻り値のデータ型と
同じデータ型の値を指定するか、暗黙的なデータ変換で
第3引数の戻り値のデータ型と同じデータ型となるような値を
指定しなければなりません。
数値を一重引用符(')で囲んで文字列として指定している場合、
DECODE関数の最初の戻り値の型が数値型だった場合には、
暗黙的なデータ変換が行われます。
また、DECODE関数では、単純CASE式と違ってNULL値の評価が可能です。
日付
TO_CHAR関数は、数値や日付値を指定された書式に従って文字列に変換する関数です。
第1引数に日付値が指定された場合は、日付値を文字列へ変換します。
書式は以下の通りです。
TO_CHAR('日付値' [, '日付書式'] [, 'NLSパラメータ'])
日付書式はデフォルトで埋め込みモードが有効となっています。
埋め込みモードが有効の場合、
数値が1桁の場合に先頭に0付きで表示されたり、
文字の前後にスペース付きで表示されます。
埋め込みモードを無効にするには、日付書式に「FM」を指定します。
<<例>>-------------------------------------------------------------------
・SELECT TO_CHAR(SYSDATE, 'fmDdth "of" fmMonth, fmYYYY') FROM dual;
「FM」が指定される度に埋め込みモードの有効/無効が切り替わるため、
2つ目の「FM」の指定で埋め込みモードが有効となり、
「MONTH」が埋め込みモードで表示されます。
・SELECT TO_CHAR(SYSDATE, 'Ddthfm "of" Month, YYYY') FROM dual;
「FM」は指定された箇所以降の日付書式に対して有効なため、
「DD」が埋め込みモードで表示されます。
・SELECT TO_CHAR(SYSDATE, 'Ddth "of" Month, YYYY') FROM dual;
日付書式はデフォルトで埋め込みモードが有効となっているため、
埋め込みモードで表示されます。
--------------------------------------------------------------------------□
日をスペル表記かつ順序表記にするためには、
日付書式要素「Dd」にスペル表記を表す接尾辞「sp」と
順序表記を表す「th」を指定します。
| 種類 | 表記 | 表示例 |
|---|---|---|
| th | 順序表記 | 18th |
| sp | スペル表記 | Eighteen |
また、書式中に文字列を含める場合は、
含める文字列を二重引用符(")で囲んで指定します。
「SP」,「TH」などは日付書式要素の後ろに指定します。
前に指定するとエラーとなります。
●「YY」と「RR」の違い
日付書式の要素である「YY」と「RR」はどちらも年の下2桁を表しますが、
世紀の取扱に違いがあります。
「YY」は引数で指定された値を常に現在の世紀の値として扱うので、
「95」では2095年に変換されます。
「RR」は現在の世紀が世紀の前半であるか後半であるか、
指定された値が世紀の前半であるか後半であるかで世紀が決定されます。
「95」は、1995年に変換されます。
数値書式
数値書式は数値を文字列に変換するフォーマットです。
数値著式に使用できる主な要素は、下記の通りです。
| 要素 | 説明 | 例 | 変更後 |
|---|---|---|---|
| 9 | 指定された桁数の値を表示 | 999999 | 1234 |
| 0 | 先行0または後続0を表示 | 0999999 | 001234 |
| $ | $記号の表示 | $99999 | $1234 |
| L | ローカルの通貨記号の表示 | L99999 | ¥1234 |
| . | 指定された位置に小数点を表示 | 999.999 | 1.234 |
| D | 指定された位置に小数点を表示 | 999D999 | 1.234 |
| , | 指定された位置にカンマを表示 | 999,999 | 1,234 |
| G | 指定された位置にカンマを表示 | 999G999 | 1,234 |
| S | +または-記号を表示 | S999999 | +1234 or -1234 |
| V | 10のn乗を乗算した値を表示 | 9999V99 | 123400 |
| MI | (負の値の場合)右側に-記号を表示 | 999999MI | 1234- |
| PR | (負の値の場合)<>で囲んで表示 | 999999PR | <1234> |
次回
土曜日にしたネイルがバキバキに取れてきて、
ベリベリ取りながら問題を解いていたので、
これを書き終わる今、私の机回りは赤いネイルの破片だらけです。。。
汚い。。。
お掃除が終わったら、はちみつレモンでも入れて
一息ついたら次の分野にとりかかります。。。
随時更新中＠あんず飴