SQLで使える関数の基礎知識：ORACLE MASTER Bronze SQL基礎I 講座（3）
Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう！（参考記事：「Oracle 10g対応のORACLE MASTERとは？」）
前回「SQL問い合わせによる行の制限とソート」で、WHERE句による行の制限とORDER BY句による行のソートを紹介しました。今回と次回の2回にわたって、SQLで使う関数のうち、単一行関数を学びましょう。
ORACLE MASTER Bronze SQL基礎I 講座 各回のインデックス
第3回 SQLで使える関数の基礎知識
理解しておきたいこと
1．確認しておきたい内容
- SQLで使用できる各種関数
- SELECT文における文字、数値、日付関数の使用方法
2．関数のタイプ
関数とは、データを基にした計算、データ型の変更、表示書式の変更などを行うプログラムです。SQL文の中で関数を使用することで、変換された1つの結果を戻すことができます。関数は、入力値の違いによって2種類に分かれます。
| 関数 | 説明 |
|---|---|
| 単一行関数 | 1行の入力値に対し、1つの値を戻す |
| グループ関数 | 複数行の入力値に対し、1つの値を戻す |
入力値として複数の引数を受け取ることができ、ネストできるものもあります。今回と次回は、単一行関数を紹介します。
3．文字関数
文字関数は文字データ（CHAR型、VARCHAR型）を受け取り、文字データ（一部数値データもあり）を戻します。多くの文字関数がありますが、以下のものは覚えておきましょう。
| 文字関数 | 説明 | 例 |
|---|---|---|
| LOWER | 小文字に変換 | LOWER('ABC')→abc |
| UPPER | 大文字に変換 | UPPER('abc')→ABC |
| INITCAP | 単語の先頭を大文字、残りを小文字に変換 | INITCAP('ABC DEF')→Abc Def |
| CONCAT | 文字データを連結。CONCAT('str1', 'str2')のように2つの引数を取り、連結して戻す。||演算子と同じ | CONCAT('ABC', 'DEF')→ABCDEF |
| SUBSTR | 文字データの切り出し。SUBSTR('str', m, n)のように3つの引数を取り、文字列strのm番目から、nで指定した数の文字を戻す ・mが負の値の場合は、終わりからm番目の文字位置となる ・nを省略した場合は、文字列末尾までを戻す |
SUBSTR('ABCDE', 2, 3)→BCD SUBSTR('ABCDE', -3, 2)→CD SUBSTR('ABCDE', 2)→BCDE |
| LENGTH | 引数で指定した文字データの文字数を戻す | LENGTH('ABCD')→4 |
| INSTR | 文字位置を戻す。INSTR('str1', 'str2', m, n)のように4つの引数を取り、文字列str1に文字列str2が出現した位置を戻す。mとnはオプションで、検索開始位置と出現回数を指定できる。mとnのデフォルトは共に1 | INSTR('ABCABC', 'B')→2 INSTR('ABCABC', 'B', 1, 2)→5 |
| LPAD | 文字データを右ぞろえにする。LPAD('str1', m, 'str2')のように3つの引数を取り、文字列str1がmで指定した文字数になるように左側に文字列str2を埋め込む。str2のデフォルトは空白 | LPAD('ABC', 5, 'x')→xxABC |
| RPAD | 文字データを左ぞろえにする。RPAD('str1', m, 'str2')のように3つの引数を取り、文字列str1をmで指定した文字数になるように右側に文字列str2を埋め込む。str2のデフォルトは空白 | RPAD('ABC', 5, 'x')→ABCxx |
| TRIM | 文字データの前後の文字を切り捨てる ・TRIM('str')の場合、前後の空白を切り捨てる ・TRIM('str1' FROM 'str2')の場合、str2の前後に含まれるstr1を切り捨てる ・TRIM(LEADING ['str1' FROM] 'str2'）の場合、str2の先頭に含まれるstr1を切り捨てる ・TRIM(TRAILING ['str1' FROM] 'str2'）の場合、str2の末尾に含まれるstr1を切り捨てる |
TRIM(' ABC ')→ABC TRIM('x' FROM 'xxABCxx')→ABC TRIM(LEADING 'x' FROM 'xxABCxx')→ABCxx TRIM(TRAILING 'x' FROM 'xxABCxx')→xxABC |
| REPLACE | 文字データの置換。REPLACE('str1', 'str2', 'str3')のように3つの引数を取る。文字列str1から文字列str2を検索し、検出すれば文字列str3に置換した結果を戻す | REPLACE('ABCABC', 'B', 'x')→AxCAxC |
これらの関数をテストするときは、「dual表」を使用すると便利です。dual表は、Oracleデータベースのすべてのユーザーがアクセスできるシステム表の1つで、1つの列、1つの行を持つため、関数のテストに使用できます。
例：
SQL> SELECT INITCAP('oracle database') FROM dual;
INITCAP('ORACLE
---------------
Oracle Database
問題
■問題1
"Hello World"を"ello world"に正しく変換することができる文を選択しなさい。
a．SELECT SUBSTR('Hello World', 1, 1) FROM dual;
b．SELECT LOWER(TRIM('H' from 'Hello World')) FROM dual;
c．SELECT TRUNC(SUBSTR('Hello World', 2, 1)) FROM dual;
d．SELECT TRIM('Hello World', 'H') FROM dual;
正解：b
■解説
文字列の前後の値を取り除くには、選択肢bのようにTRIM関数を使用します。選択肢にはありませんが、SUBSTRを使用してデータを切り出すこともできます。
以下の文は、2文字目以降を切り出して表示します。
SELECT SUBSTR('Hello World', 2) FROM dual;
不正解の理由は次のとおりです。
■選択肢a
1文字目から1文字分を切り出しているため、結果は「H」となります。
■選択肢c
SUBSTR関数を使用して、2文字目から1文字分を切り出した結果「e」をTRUNC関数にかけています。TRUNC関数は日付データか数値データしか扱えないため、エラーとなります。
■選択肢d
TRIM関数の使用法として間違っています。前後の空白を取り除くのであれば「TRIM(' Hello World ')」とできますが、指定した文字を取り除くには「TRIM('H' from 'Hello World')」のように記述します。
■問題2
文字操作関数を3つ選択しなさい。
a．TRIM
b．INSTR
c．TRUNC
d．REPLACE
e．TO_DATE
正解：a、b、d
■解説
文字操作関数は、文字データ、文字リテラルを操作できる関数です。
この問題で正解とされる関数は次の操作ができます。
■TRIM関数
文字列から先頭文字または末尾文字、あるいはその両方を切り捨てた結果を戻します。
■INSTR関数
指定した文字が左から何番目にあるのかという文字位置を戻します。
■REPLACE関数
文字列を検索して指定した値に置き換えた結果を戻します。
不正解となる選択肢cの「TRUNC」関数は数値関数と日付関数です。選択肢eの「TO_DATE」関数は変換関数です。
4. 数値関数
数値関数は数値データ（NUMBER型）を受け取り、数値データを戻します。多くの数値関数がありますが、以下のものは覚えておきましょう。
| 数値関数 | 説明 | 例 |
|---|---|---|
| ROUND | 数値データの四捨五入。デフォルトは小数点以下を四捨五入する。ROUND(m, n)のように2つの引数を取る場合、nの値が正か負かで四捨五入の位置が変わる ・nの値が正の場合、小数点以下nになるようにn+1の位置で四捨五入 ・nの値が負の場合、整数部nけたの位置で四捨五入 |
ROUND(123.5)→124 ROUND(123.456, 2)→123.46 ROUND(150.45, -2)→200 |
| TRUNC | 数値データの切り捨て。デフォルトは小数点以下を切り捨てる。TRUNC(m, n)というように2つの引数を取る場合、nの値が正か負かで切り捨て位置が変わる ・nの値が正の場合、小数点以下がnになるようにn+1の位置で切り捨て ・nの値が負の場合、整数部nけたの位置で切り捨て |
TRUNC(123.5)→123 TRUNC(123.456, 2)→123.45 TRUNC(150.45, -2)→100 |
| MOD | MOD(m, n)のように2つの引数を取り、mをnで除算した余りを戻す | MOD(10, 3)→1 |
ROUND関数とTRUNC関数は第2引数によって結果が変わってきます。どのような結果になるのか、dual表を使用してテストしておくとよいでしょう。
問題
■問題1
次の問い合わせ結果として正しいものを選択しなさい。
SELECT TRUNC(ROUND(MOD(1600, 10), -1), 2) FROM dual;
a．0
b．1
c．0.00
d．エラーとなる
正解：a
■解説
前回の宿題にした問題です。関数がネストしている場合は、内側から調べます。この問題であれば、最も内側の関数は「MOD(1600, 10)」です。MOD関数は余りを求め、1600/10の余りは「0」です。
次にROUND関数で−1が指定されていますので、整数部の1の位で四捨五入が行われます。元の値が0ですから、結果も「0」です。
最後にTRUNC関数で2が指定されていますので、小数部の2けた目で切り捨てが行われます。元の値が0ですのでやはり「0」となります。
選択肢cの「0.00」は迷うところですが、データの表示書式を明示的に変更していない限り、「0」が「0.00」と表示されることはありません。
5. 日付関数
日付関数は日付データ（DATE型）を受け取り、日付データ（一部数値データもあり）を戻します。多くの日付関数がありますが、以下のものは覚えておきましょう。
| 日付関数 | 説明 | 例 |
|---|---|---|
| SYSDATE | 現在の日付と時刻を戻す | SYSDATE→'2005-07-15 13:25:55' |
| MONTHS_BETWEEN | 2つの日付間の月数を求める。 MONTHS_BETWEEN('date1', 'date2')のように2つの引数を取り、その間の月数（日以下は小数）を表示。date1よりdate2の方が大きい場合は、負の値として表示する |
MONTHS_BETWEEN('2005-07-15', '2004-07-10')→12.1612903 MONTHS_BETWEEN('2004-07-10', '2005-07-15')→-12.1612903 |
| ADD_MONTHS | 日付に月数を加算する。ADD_MONTHS('date', m)のように2つの引数を取り、dateにm月を加算する。mの値が負の場合は、m月を減算する | ADD_MONTHS('2005-01-15', 6)→'2005-07-15' DD_MONTHS('2005-01-15', -6)→'2004-07-15' |
| NEXT_DAY | 次の指定曜日の日付を求める。NEXT_DAY('date', 'day')のように2つの引数を取り、dateの後のday曜日の日付を戻す | NEXT_DAY('2005-07-12','金曜日')→'2005-07-15' |
| LAST_DAY | その月の月末の日付を求める | LAST_DAY('2005-07-15')→'2005-07-31' |
| ROUND | 日付を四捨五入する。デフォルトは時刻（時間、分、秒）を四捨五入する（午前11時59分59秒を境とする）。ROUND('date', 'str')のように2つの引数を取る場合、strに指定した日付要素で四捨五入の位置が決まる ・年('YYYY')→6月30日を境とする ・月('MM')→15日を境とする ・日('DD')→午前11時59分59秒を境とする ・時間('HH24')→29分を境とする ・分('MI')→29秒を境とする |
ROUND('2005-07-01 12:00:00')→'2005-07-02 00:00:00' ROUND('2005-07-01 10:30:55', 'YYYY')→'2006-01-01 00:00:00' ROUND('2005-07-16 10:30:55', 'MM')→'2005-08-01 00:00:00 ROUND('2005-07-16 12:00:00', 'DD')→'2005-07-17 00:00:00' ROUND('2005-07-16 12:00:30', 'MI')→'2005-07-16 12:01:00' |
| TRUNC | 日付を切り捨てる。デフォルトは時刻（時間、分、秒）を切り捨てる（その日の午前0時にする）。TRUNC ('date', 'str')のように2つの引数を取る場合、strに指定した日付要素で切り捨て位置が決まる ・年（'YYYY'）→その年の1月1日にする ・月（'MM）→その月の1日にする ・日（'DD'）→その日の午前0時にする ・時間（'HH24'）→その時間の00分にする ・分('MI')→その分の00秒にする |
TRUNC('2005-07-01 12:00:00')→'2005-07-01 00:00:00' TRUNC('2005-07-01 10:30:55','YYYY')→'2005-01-01 00:00:00' TRUNC('2005-07-16 10:30:55','MM')→'2005-07-01 00:00:00' TRUNC('2005-07-16 12:00:00', 'DD')→'2005-07-16 00:00:00' TRUNC('2005-07-16 12:00:30', 'MI')→'2005-07-16 12:00:00' |
また、日付データに関しては、関数だけでなく計算時の特徴も覚えておく必要があります。
| 演算 | 結果 | 説明 | 例 |
|---|---|---|---|
| 日付＋数値 | 日付 | 日付に日数を加算する | '2005-07-15'+10→2005-07-25 |
| 日付−数値 | 日付 | 日付から日数を減算する | '2005-07-15'-10→2005-07-05 |
| 日付−日付 | 日数 | 日付間の日数を求める | '2005-07-15'-'2005-07-10'→5 |
| 日付＋数値/24 | 日付 | 日付に時間を加算する（n/24） | '2005-07-15 00:00:00'+2/24→'2005-07-15 02:00:00' |
| 日付＋数値/1440 | 日付 | 日付に分を加算する（n/24*60） | '2005-07-15 00:00:00'+30/1440→'2005-07-15 00:30:00' |
| 日付＋数値/86400 | 日付 | 日付に秒を加算する（n/24*60*60） | '2005-07-15 00:00:00'+30/86400→'2005-07-15 00:00:30' |
「日付−日付」はできますが、「日付＋日付」はできないことに注意してください。また、日付データの計算をdual表でテストするとき、日付データとして変換できないとエラーになることがあります。そのような場合は、TO_DATE関数を使用して変換した後にテストしてください。
例：
SQL> SELECT TO_DATE('2005-07-15')+10 FROM dual;
TO_DATE('2
----------
2005-07-25
日付データは、時間部分を記述していなくても内部的に4けたの年、月、日、時間、分、秒でデータを持ちます。デフォルトでは年、月、日のみ表示されるので、時間部分を出力する場合は、次のようにNLS_DATE_FORMATセッションパラメータを変更しておくとよいでしょう。
例：
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SQL> SELECT SYSDATE FROM dual;
SYSDATE
-------------------
2005-07-13 12:26:01
YYYYなどの意味は、次回の変換関数で説明します。
問題
■問題1
2001年3月19日というデータを、"Nineteenth of Mar 2001 12:00:00 AM"と表示する文を選択しなさい。
a．SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth of Mon YYYY HH:MI:SS AM') FROM dual;
b．SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Mon YYYY HH:MI:SS AM') FROM dual;
c．SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'fmDdspth "of" Mon YYYY fmHH:MI:SS AM') FROM dual;
d．SELECT TO_CHAR(TO_DATE('19-Mar-2001', 'DD-Mon-YYYY'), 'Ddspth "of" Mon YYYY fmHH:MI:SS AM') FROM dual;
正解：c
■解説
日付の書式要素は非常に多くあります。この問題でいえば、ポイントは次のとおりです。
■Nineteenth
「19」という数値を英字つづりにするには「SP」を使用します。「〜th」で終わりにするには「SPTH」を使用します。日付は「DD」ですから、「DDSPTH」でこの値が表現できます。
■of
文字リテラルを含めるには「"of"」というように二重引用符で囲みます。
■12:00:00 AM
もともと値として存在しなかった時刻部分を表示するには、「DDSPTH」と「HH:MI:SS」の前に「FM」を追加して、前後に空白を付けずにそのまま値を戻すようにします。また、「AM」という正午標識は、そのまま「AM」と指定することで評価されます。
不正解の理由は次のとおりです。
■選択肢a
「of」という文字リテラルは二重引用符で囲む必要があります。
■選択肢b
「Nineteenth of Mar 2001 12:0:0 AM」という結果になります。時刻側（HH:MI:SS）にも「FM」を付ける必要があります。
■選択肢d
「Nineteenth of Mar 2001 12:0:0 AM」という結果になります。日付側（Dpspth）にも「FM」を付ける必要があります。
■問題2
数値を戻す問い合わせを選択しなさい。
a．SELECT TO_NUMBER(SYSDATE+7) FROM dual;
b．SELECT SYSDATE-hiredate FROM employees;
c．SELECT ADD_MONTHS(hiredate, 7) FROM employees;
d．SELECT SYSDATE - 7 FROM dual;
正解：b
■解説
日付データの計算では、次のことを実行できます。
■日付に数値を加算または減算して日付値を取得
■数値を24で除算した数値を加算することで日付値に時間を加算
■2つの日付間を減算し、その日付間の日数を取得
従って、日付データ同士の減算であれば、日数として数値が戻せます。この問題では明記されていませんが、EMPLOYEES表のHIREDATE列は日付データですので、「SELECT SYSDATE-hiredate FROM employees;」は現在の日付から入社日を減算した数値を戻します。
不正解の理由は次のとおりです。
■選択肢a
日付データに数値を加算すると、加算後の日付データを戻します。日付データにTO_NUMBER関数を使用することはできず、エラーとなります。
■選択肢c
ADD_MONTHSは、1つ目の引数に2つ目の月数を加算した日付データを戻します。
■選択肢d
日付データから数値を減算すると、減算後の日付データを戻します。
宿題
次回は、今回に引き続き単一行関数を確認します。次の宿題を解いておいてください。
関数に関する説明として正しいものを3つ選択しなさい。
a．INSTRは、文字の位置を戻す
b．TRUNCATEは、文字、日付、数値の切り捨てを行う
c．DECODEは、文字を比較し、変換することができる
d．NVLは、2つの値を比較し、等しかったらNULLを戻し、等しくなかったら式1を戻す
e．NULLIFは、2つの値を比較し、等しかったらNULLを戻し、等しくなかったら式1を戻す
IT資格試験の模擬問題をWebベースで学習できる＠IT自分戦略研究所の新サービス「＠IT資格攻略」では、Oracle関連の資格をテーマとして取り上げています。Bronze SQL 基礎 I、Bronze DBA 10gも近日中に追加予定です。「無料お試し版」もありますので、記事と併せてご覧ください。
Copyright © ITmedia, Inc. All Rights Reserved.