Lesson４ 関数
SQLには与えられた表に対する演算結果を返す、通常のプログラミング言語で扱われるような関数も沢山存在します。ただし、ANSI/ISOで標準化されている集計関数は
COUNT, AVG, MAX, MIN, SUM の５つだけです。
ここでは、これら集計関数と各データベース環境に代表されるさまざまな関数についても扱います。
4.1 標準集計関数
関数はあなたが使用しているリレーショナルデータベースによって数十から数百まで、さまざまな種類を提供しているものがあります。つまり、これらの関数を使用する限り、互換性は保証されません。逆にANSI/ISOで定義されている集計関数
COUNT, AVG, MAX, MIN, SUM の場合は殆どのデータベースで使用でき、互換性も保証されると考えて良いでしょう。
これらの、関数は次のような意味があります。
COUNT 列の個数を返します。
AVG 列の平均を返します。
MAX 列の最大値を返します。
MIN 列の最小値を返します。
SUM 列の合計を返します。
では、実際にこれら関数を使用して動作を確かめてみましょう。
次のSQL文は SUM関数を使って NO 列の合計を求めます。
SELECT SUM( NO ) FROM TABLE;
結果は次のようになりました。
SUM(NO)
----- 30
全ての行のNO列の合計が結果として出力されています。
他の関数についても基本的に動作は同じです。実際に試してみると良いでしょう。
4.2 その他の関数
関数の種類は使用しているデータベース環境によりさまざまであるということは既にお話しました。これらの関数は、同じ機能の関数でもデータベースによっては異なる関数名である場合もあります。また、同じ関数名でも動作が異なる場合もあります。
ここで全ての関数について使用方法や使い方を述べるのは残念ながら不可能です。各種自分が使用しているマニュアルを参照すると良いでしょう。
4.3 追加関数（特別企画）
本書に記載されている関数はその重要度と使用方法という点での必要性から選択された種類のものです。これらの関数を自由に使用できるようになれば大体他の関数でも応用が効くようになるでしょう。
しかしながら、実は原書には更に倍ぐらいの関数についての記述があります。これらは、本書の構成上の問題や限られた紙面の関係から削られました。削除されたSQL文ももちろん検証は行っていますし、憶えておいても損はありません。また、より原書にちかいものになります。ここでは、これらの関数について重要度が低いながらも全て御紹介したいかと思います。
なお、実際に使用している表のデータの詳細は本書を参照ください。
VARIANCE
VARIANCEは、分散を計算します。分散は、統計計算では重要な数値です。 たとえば、次のように使います。
SQL> SELECT VARIANCE(HITS)
FROM TEAMSTATS;
VARIANCE(HITS)
--------------
802.96667
1行が選択されました。
STDDEV
STDDEVは、数値からなる列の標準偏差を求めます。たとえば、次のように使います。
SQL> SELECT STDDEV(HITS)
FROM TEAMSTATS;
STDDEV(HITS)
------------
28.336666
1行が選択されました。
VARIANCEおよびSTDDEVも使用できるのは数値に対してだけです。
これまでに取り上げた集合関数は、さまざまな方法で組み合わせることができます。
SQL> SELECT COUNT(AB),
AVG(AB),
MIN(AB),
MAX(AB),
STDDEV(AB),
VARIANCE(AB),
SUM(AB)
FROM TEAMSTATS;
COUNT(AB) AVG(AB) MIN(AB) MAX(AB) STDDEV(AB) VARIANCE(AB) SUM(AB)
--------- ------- ------- ------- ---------- ------------ -------
6 119.167 1 187 75.589 5712.97 715
1行が選択されました。
野球の実況アナウンサーはゲームの途中でさまざまな統計情報を教えてくれますが、 きっと舞台裏では、このクエリーのようにSQLが利用されていることでしょう。
日付と時刻の関数
現代人は日付と時刻によって細かく管理された社会に生きていますが、ほとんどの主要な SQL処理系も、こうした日付と時刻の概念を処理するための関数を提供しています。 ここでは、次のようなPROJECTテーブルを使って日付と時刻の関数を説明します。
┌─ PROJECTテーブル ──────────┐
│ │
│ TASK STARTDAT ENDDATE │
│ -------------- -------- -------- │
│ KICKOFF MTG 98-04-01 98-04-01 │
│ TECH SURVEY 98-04-02 98-05-01 │
│ USER MTGS 98-05-15 98-05-30 │
│ DESIGN WIDGET 98-06-01 98-06-30 │
│ CODE WIDGET 98-07-01 98-09-02 │
│ TESTING 98-09-03 99-01-17 │
│ │
└─────────────────────┘
このテーブルは、日付型というデータ型を使っています。ほとんどのSQL処理系には、 日付型というデータ型がありますが、細かな構文は処理系によって異なることがあります。
ADD_MONTHS
ADD_MONTHS関数は、指定した日付に一定の月数を加えます。 たとえば、予測できない事態が発生したために、上のプロジェクトのスケジュールが2か月 延びるとします。 この場合、次のようにすると新しいスケジュールを簡単に作成できます。
SQL> SELECT TASK,
STARTDATE,
ENDDATE ORIGINAL_END,
ADD_MONTHS(ENDDATE,2)
FROM PROJECT;
TASK STARTDAT ORIGINAL ADD_MONT
-------------- -------- -------- --------
KICKOFF MTG 98-04-01 98-04-01 98-06-01
TECH SURVEY 98-04-02 98-05-01 98-07-01
USER MTGS 98-05-15 98-05-30 98-07-30
DESIGN WIDGET 98-06-01 98-06-30 98-08-31
CODE WIDGET 98-07-01 98-09-02 98-11-02
TESTING 98-09-03 99-01-17 99-03-17
6行が選択されました。
この関数の便利なところは、簡単に日付を計算できる点です。 ADD_MONTHSは、次のようにSELECT句の内部で使用することもできます。
SQL> SELECT TASK TASKS_SHORTER_THAN_ONE_MONTH
FROM PROJECT
WHERE ADD_MONTHS(STARTDATE,1) > ENDDATE;
TASKS_SHORTER_
--------------
KICKOFF MTG
TECH SURVEY
USER MTGS
DESIGN WIDGET
4行が選択されました。
日付と時刻の関数はいずれも、このようにいろいろな場所で使用できます。 ただし、ADD MONTHSは、後で説明するTO_CHARやTO_DATEなどの助けを借りなければ、 文字や数値など、ほかのデータ型に対して使用することはできません。
LAST_DAY
LAST_DAYは、指定した月の最後の日を返します。 たとえば、ENDDATE列の月の最後の日を調べる必要があるときは、次のように入力します。
SQL> SELECT ENDDATE, LAST_DAY(ENDDATE)
FROM PROJECT;
ENDDATE LAST_DAY
-------- --------
98-04-01 98-04-30
98-05-01 98-05-31
98-05-30 98-05-31
98-06-30 98-06-30
98-09-02 98-09-30
99-01-17 99-01-31
6行が選択されました。
LAST_DAYは閏年をどのように処理するのでしょうか。
SQL> SELECT LAST_DAY('98-02-01') NON_LEAP,
LAST_DAY('2000-02-01') LEAP
FROM PROJECT;
NON_LEAP LEAP
-------- --------
98-02-28 00-02-29
98-02-28 00-02-29
98-02-28 00-02-29
98-02-28 00-02-29
98-02-28 00-02-29
98-02-28 00-02-29
6行が選択されました。
正しい結果が得られました。しかし、複数の行が返されているのはなぜでしょうか。 このクエリーでは特定の列や条件を指定していないため、SQLは文の中で使われている 日付関数をすべての行に対して適用したのです。 もっと簡潔な出力を得るには、次のようにします。
SQL> SELECT DISTINCT LAST_DAY('98-02-01') NON_LEAP,
LAST_DAY('2000-02-01') LEAP
FROM PROJECT;
NON_LEAP LEAP
-------- --------
98-02-28 00-02-29
1行が選択されました。
この文ではDISTINCTという語を使って結果が1つだけ出力されるようにしています。 出力結果をみてわかるように、この関数は閏年とそうでない年とを区別しています。 ただし、この種の関数を使うときは、各処理系のマニュアルをよく読む必要があります。
MONTHS_BETWEEN
MONTHS_BETWEENは、x月からy月まで何か月あるかを調べるときに使います。 たとえば、次のように使います。
SQL> SELECT TASK, STARTDATE, ENDDATE,MONTHS_BETWEEN(STARTDATE,ENDDATE)
DURATION
FROM PROJECT;
TASK STARTDAT ENDDATE DURATION
-------------- -------- -------- ---------
KICKOFF MTG 98-04-01 98-04-01 0
TECH SURVEY 98-04-02 98-05-01 -.9677419
USER MTGS 98-05-15 98-05-30 -.483871
DESIGN WIDGET 98-06-01 98-06-30 -.9354839
CODE WIDGET 98-07-01 98-09-02 -2.032258
TESTING 98-09-03 99-01-17 -4.451613
6行が選択されました。
結果が、マイナスの値になっています。期待したものと少し違うようです。 次のように修正しましょう。
SQL> SELECT TASK, STARTDATE, ENDDATE,
MONTHS_BETWEEN(ENDDATE,STARTDATE) DURATION
FROM PROJECT;
TASK STARTDAT ENDDATE DURATION
-------------- -------- -------- ---------
KICKOFF MTG 98-04-01 98-04-01 0
TECH SURVEY 98-04-02 98-05-01 .96774194
USER MTGS 98-05-15 98-05-30 .48387097
DESIGN WIDGET 98-06-01 98-06-30 .93548387
CODE WIDGET 98-07-01 98-09-02 2.0322581
TESTING 98-09-03 99-01-17 4.4516129
6行が選択されました。
今度は正しく表示されました。このように、MONTHS_BETWEENは2つの月を指定する順序 によって返す値が異なります。負の月数にも利用価値はあります。 たとえば、負の月数を利用すれば、ある日付がもう一方の日付よりも前かそれとも後かを調 べることができることです。 次の文は、このことを使って、1998年5月19日よりも前に開始されたすべての作業を検索し ます。
SQL> SELECT *
FROM PROJECT
WHERE MONTHS_BETWEEN('98-05-19', STARTDATE) > 0;
TASK STARTDAT ENDDATE
-------------- -------- --------
KICKOFF MTG 98-04-01 98-04-01
TECH SURVEY 98-04-02 98-05-01
USER MTGS 98-05-15 98-05-30
3行が選択されました。
NEXT_DAY
NEXT_DAYは、指定した日付と同じかまたはそれより後で、該当する最初の曜日の日付を 返します。 たとえば、各作業の開始日以後の最初の金曜日にレポートを送るには、次のようにして目的 の日付を探します。
SQL> SELECT STARTDATE,
NEXT_DAY(STARTDATE, '金曜日')
FROM PROJECT;
STARTDAT NEXT_DAY -------- -------- 98-04-01 98-04-03 98-04-02 98-04-03 98-05-15 98-05-22 98-06-01 98-06-05 98-07-01 98-07-03 98-09-03 98-09-04 6行が選択されました。
STARTDATE以降で最初の金曜日の日付が出力されます。
SYSDATE
SYSDATEは、システムの日付を返します。
SQL> SELECT DISTINCT SYSDATE
FROM PROJECT;
SYSDATE
--------
98-06-29
1行が選択されました。
たとえば、今日現在でのプロジェクトの状態を調べるには、次のようにします。 プロジェクトのうち、今日以降に開始予定の作業が表示されます。
SQL> SELECT *
FROM PROJECT
WHERE STARTDATE > SYSDATE;
TASK STARTDAT ENDDATE
-------------- -------- --------
CODE WIDGET 98-07-01 98-09-02
TESTING 98-09-03 99-01-17
COS、COSH、SIN、SINH、TAN、およびTANH
COS、SIN、およびTAN関数は、三角法のさまざまな概念をサポートする関数です。 これらの関数はいずれも、引数にラジアンを指定してします。 たとえば、次の文は、COS関数がAをラジアンとして扱うことを知らないと、予期しない値を返します。
SQL> SELECT A, COS(A)
FROM NUMBERS1;
A COS(A) --------- ---------
3.1415 -1
-45 .52532199
5 .28366219
-57.667 .437183
15 -.7596879
-7.2 .60835131
6行が選択されました。
45度のコサインが約0.707になることを期待していましたが、出力された結果では約0.525に なっています。 現実の世界では、180度の転換という言い方はよく聞きますが、π(パイ)ラジアンの転換という 表現はまず使いません。 同様に、COS関数でも度を扱うようにするには、度をラジアンに変換する必要があります。 360度は2πですから、次のように記述できます。 SQL> SELECT A, COS(A* 0.01745329251994)
FROM NUMBERS1;
A COS(A*0.01745329251994) --------- -----------------------
3.1415 .99849724
-45 .70710678
5 .9961947
-57.667 .5348391
15 .96592583
-7.2 .9921147
6行が選択されました。
0.01745329251994という値は、度で割ったラジアンであることに注意してください。 三角関数を使った結果は次のようになります。
SQL> SELECT A, COS(A*0.017453), COSH(A*0.017453)
FROM NUMBERS1;
A COS(A*0.017453) COSH(A*0.017453) --------- --------------- ----------------
3.1415 .99849729 1.0015035
-45 .70711609 1.3245977
5 .99619483 1.00381
-57.667 .53485335 1.5507072
15 .96592696 1.0344645
-7.2 .99211497 1.0079058
6行が選択されました。
次はSINとSINHの例です。
SQL> SELECT A, SIN(A*0.017453), SINH(A*0.017453)
FROM NUMBERS1;
A SIN(A*0.017453) SINH(A*0.017453) --------- --------------- ----------------
3.1415 .05480113 .05485607
-45 -.7070975 -.8686535
5 .08715429 .0873758
-57.667 -.8449449 -1.185197
15 .25881481 .26479569
-7.2 -.1253311 -.1259926
6行が選択されました。
次はTANとTANHの例です。
SQL> SELECT A, TAN(A*0.017453), TANH(A*0.017453)
FROM NUMBERS1;
A TAN(A*0.017453) TANH(A*0.017453) --------- --------------- ----------------
3.1415 .05488361 .05477372
-45 -.9999737 -.6557867
5 .08748719 .08704416
-57.667 -1.579769 -.7642948
15 .26794449 .25597369
-7.2 -.1263272 -.1250043
6行が選択されました。
EXP
EXPを使うと、eを底としたべき乗を計算できます(eは、さまざまな公式で使われる数学定数です。） 次に示すのは、列Aの値を指数とするeのべき乗を計算する例です。
SQL> SELECT A, EXP(A)
FROM NUMBERS1;
A EXP(A) --------- ---------
3.1415 23.138549
-45 2.863E-20
5 148.41316
-57.667 9.027E-26
15 3269017.4
-7.2 .00074659
6行が選択されました。
LNとLOG
LNとLOGは、対数関係の関数です。LNは、指定した引数の自然対数を返します。 引数には正の数を指定する必要があります。
SQL> SELECT A, LN(ABS(A))
FROM NUMBERS1;
A LN(ABS(A)) --------- ----------
3.1415 1.1447004
-45 3.8066625
5 1.6094379
-57.667 4.0546851
15 2.7080502
-7.2 1.974081
6行が選択されました。
この例では、LNの呼び出しの内側にABS関数を埋め込んでいますが、このような使い方もできることに注意してください。 もう1つの対数関数であるLOGは2つの引数を取り、2番目の引数を底とする最初の引数の対数を返します。 次のクエリーは、10を底として列Bの対数を返します。
SQL> SELECT B, LOG(B, 10)
FROM NUMBERS1;
B LOG(B,10) ----------- ---------
4 1.660964
.707 -6.640962
9 1.0479516
42 .61604832
55 .57459287
5.3 1.3806894
6行が選択されました。
MOD
MODは既に取り上げたことがあります。第３章の「式、条件、演算子」で、剰余演算子%の ANSI規格がMODという名前で実装されていることがあると述べました。 次に示すのは、AをBで割った余りの表を返すクエリーです。
SQL> SELECT A, B, MOD(A,B)
FROM NUMBERS1;
A B MOD(A,B) --------- --------- ---------
3.1415 4 3.1415
-45 .707 -.459
5 9 5
-57.667 42 -15.667
15 55 15
-7.2 5.3 -1.9
6行が選択されました。
POWER
数値のほかの数値でべき乗するには、POWERを使います。 POWER関数は、最初の引数を2番目の引数でべき乗したものを返します。
次のクエリーの結果はどうなるでしょうか。
SQL> SELECT A, B, POWER(A,B)
FROM NUMBERS1;
エラー:
ORA-01428: 引数: -45が有効範囲外です。
レコードが選択されませんでした。
最初の引数に負の数は指定できないように思えますが、実際はそうではありません。 たとえば、-4のような負の数のべき乗も求めることができます。 つまり、POWER関数の最初の引数が負の数である場合、2番目の引数は整数でなければならないのです。 この例の場合、CEIL(またはFLOOR)を使うと2番目の引数を整数にすることができます。
SQL> SELECT A, CEIL(B), POWER(A,CEIL(B))
FROM NUMBERS1;
A CEIL(B) POWER(A,CEIL(B)) --------- --------- ----------------
3.1415 4 97.3976
-45 1 -45
5 9 1953125
-57.667 42 9.098E+73
15 55 4.842E+64
-7.2 6 139314.07
6行が選択されました。
今度は問題なく結果が表示されました。
文字関数
文字や文字列を操作する関数は多くのSQL処理系が提供していますが、ここでは、 このうちよく使われるものについて説明します。 次のようなCHARACTERSテーブルを使います。
┌─ CHARACTERSテーブル ───────────────┐
│ │
│ LASTNAME FIRSTNAME M CODE │
│ --------------- --------------- - ---------│
│ PURVIS KELLY A 32│
│ TAYLOR CHUCK J 67│
│ CHRISTINE LAURA C 65│
│ ADAMS FESTER M 87│
│ COSTALES ARMANDO A 77│
│ KONG MAJOR G 52│
│ │
└───────────────────────────┘
CHR
CHRは、引数に指定した数値に対応する文字を返します。 CHRが返す文字は、データベースの文字セットによって異なります。 ここで例として使うデータベースの文字セットはASCIIになっており、CODE列には数値が含まれています。
SQL> SELECT CODE, CHR(CODE)
FROM CHARACTERS;
CODE CH --------- --
32
67 C
65 A
87 W
77 M
52 4
6行が選択されました。
ASCII文字セットでは32がスペースなので、コード列の32に対応するCH列は空白になっています。
CONCAT
第３章の「式、条件、演算子」で演算子について学習したときに、CONCAT関数と同じ機能を持つ||演算子を使いました。 CONCATは、二重パイプ記号(||)同様、2つの文字列を連結します。次のように使います。
SQL> SELECT CONCAT(FIRSTNAME, LASTNAME) "FIRST AND LAST NAMES"
FROM CHARACTERS;
FIRST AND LAST NAMES ------------------------ KELLY PURVIS CHUCK TAYLOR LAURA CHRISTINE FESTER ADAMS ARMANDO COSTALES MAJOR KONG
6行が選択されました。
ここでは、複数の語からなるFIRST AND LAST NAMESという別名を二重引用符で囲んでいます。 ただし、複数の語からなる別名を使用できるどうかについては、各処理系のマニュアルを参照してください。
また、上の出力では表が2つの列から構成されているように見えますが、実際には1つの列しかありません。 連結している2つの列のうち、最初の列(FIRSTNAME)の値は15文字分の幅があり、15文字に満たない 文字列の場合は後ろにスペースが保管されていることに注意してください。
INITCAP
INITCAPは、語の先頭の文字を大文字にし、残りの文字をすべて小文字にします。
SQL> SELECT FIRSTNAME BEFORE, INITCAP(FIRSTNAME) AFTER
FROM CHARACTERS;
BEFORE AFTER -------------- ----------
KELLY Kelly
CHUCK Chuck
LAURA Laura
FESTER Fester
ARMANDO Armando
MAJOR Major
6行が選択されました。
LPADとRPAD
LPADとRPADは、少なくとも2つの引数、最大で3つの引数を取ります。 最初の引数には、操作の対象となる文字列を指定します。 2番目の引数には、最初の引数で指定した文字列を埋め込む領域の文字幅を指定します。 省略可能な3番目の引数には、指定した領域が最初の文字幅より大きかった場合に埋め込む文字を指定します。 3番目の文字の既定値は空白1つですが、任意の1文字または文字列を指定することができます。 たとえば、次のように入力すると、15文字のフィールドであるLASTNAMEに埋め込み文字が5つ追加されます。
SQL> SELECT LASTNAME, LPAD(LASTNAME,20,'*')
FROM CHARACTERS;
LASTNAME LPAD(LASTNAME,20,'*' -------------- --------------------
PURVIS *****PURVIS
TAYLOR *****TAYLOR
CHRISTINE *****CHRISTINE
ADAMS *****ADAMS
COSTALES *****COSTALES
KONG *****KONG
6行が選択されました。
なぜ埋め込み文字は5つしか追加されないのでしょうか。 それは、LASTNAMEの名前の後にもともと空白があり、全て15文字に調整されていたからです。 ただし、列のデータ型によっては、列に割り当てられている最大幅よりも列の値の幅が少ないと、 埋め込み文字が削除される場合もあります。 実際にどのような動作をするかについては、各処理系のマニュアルを参照してください。 さて、今度は右側に文字を埋め込みましょう。
SQL> SELECT LASTNAME, RPAD(LASTNAME,20,'*')
FROM CHARACTERS;
LASTNAME RPAD(LASTNAME,20,'*'
--------------- --------------------
PURVIS PURVIS *****
TAYLOR TAYLOR *****
CHRISTINE CHRISTINE *****
ADAMS ADAMS *****
COSTALES COSTALES *****
KONG KONG *****
6行が選択されました。
この出力を見ると、空白がフィールド名の一部として扱われていることがよくわかります。 次に、このような場合に使うと便利な関数を紹介します。
REPLACE
REPLACEは、置換を行う関数です。 指定する引数は2つまたは3つで、最初の引数には検索する文字列を指定します。 2番目の引数には検索した文字列中の対象となる文字または文字列を指定します。 省略可能な3番目の引数には、置換する文字列を指定します。 3番目の引数を省略するかまたはNULLを指定した場合は対象となる文字が削除されます。
SQL> SELECT LASTNAME, REPLACE(LASTNAME, 'ST') REPLACEMENT
FROM CHARACTERS;
LASTNAME REPLACEMENT --------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRIINE
ADAMS ADAMS
COSTALES COALES
KONG KONG
6行が選択されました。
3番目の引数を指定した場合は、対象となる文字列が、指定した引数で置き換えられます。 たとえば、次のように使います。
SQL> SELECT LASTNAME, REPLACE(LASTNAME, 'ST','**') REPLACEMENT
FROM CHARACTERS;
LASTNAME REPLACEMENT --------------- ------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRI**INE
ADAMS ADAMS
COSTALES CO**ALES
KONG KONG
6行が選択されました。
TRANSLATE
TRANSLATE関数は指定した文字列から変換候補を参照して置換を行う関数です。 3つの引数を取ります。最初の引数には、操作の対象となる文字列を指定します。 2番目の引数には、変換候補となる文字列を指定します。 3番目の引数には、その候補中の置換文字列を指定します。 操作の対象となる文字列に、2番目の文字列で指定した要素が含まれていると、この文字が 3番目の文字列に対応する文字に変換されます。
SQL> SELECT FIRSTNAME, TRANSLATE(FIRSTNAME,
'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'NNNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA')
FROM CHARACTERS;
FIRSTNAME TRANSLATE(FIRSTNAME,'012345678 --------------- ------------------------------
kelly kelly
CHUCK AAAAA
LAURA AAAAA
FESTER AAAAAA
ARMANDO AAAAAAA
MAJOR AAAAA 6行が選択されました。
この関数は大文字と小文字を区別することに注意してください。
INSTR
対象の文字列中で特定の文字列がある場所を確かめるには、INSTRを使います。 最初の引数には、操作の対象となる文字列を指定します。 2番目の引数には、検索を行う文字列を指定します。 3番目の引数には、文字列中で検索を開始する位置を指定します。 4番目の引数には、検索する文字列が対象文字列中に複数存在する場合などに、何番目 に一致した位置を返すのかを指定します。
次の例は、2文字目を起点に、最初に一致した'O'の位置を返します。
SQL> SELECT LASTNAME, INSTR(LASTNAME, 'O', 2, 1)
FROM CHARACTERS;
LASTNAME INSTR(LASTNAME,'O',2,1)
--------------- -----------------------
PURVIS 0
TAYLOR 5
CHRISTINE 0
ADAMS 0
COSTALES 2
KONG 2
6行が選択されました。
3番目と4番目の引数の既定値は1です。 3番目の引数に負の数を指定すると、検索を開始する位置を、文字列の最初ではなく最後から数えた場所になります。
LENGTH
LENGTHは、引数を1つだけ取り、指定した文字列の長さを返します。 たとえば、次のように使います。
SQL> SELECT FIRSTNAME, LENGTH(RTRIM(FIRSTNAME))
FROM CHARACTERS;
FIRSTNAME LENGTH(RTRIM(FIRSTNAME)) --------------- ------------------------
kelly 5
CHUCK 5
LAURA 5
FESTER 6
ARMANDO 7
MAJOR 5
6行が選択されました。
RTRIM関数を使っていることに注意してください。 RTRIM関数を使わない場合は、どの値に対しても15が返されます。
変換関数
ここで説明する2つの変換関数は、データ型の変換を行う関数です。こ こでは、次のようなCONVERSIONSテーブルを使います。 NAME列は、幅が15文字の文字列フィールドで、TESTNUMは数値フィールドです。
┌─ CONVERSIONSテーブル ──┐
│ │
│ NAME TESTNUM │
│ --------------- ---------│
│ 40 95 │
│ 13 23 │
│ 74 68 │
│ │
└────────────────┘
TO_CHAR
TO_CHARは、主に数値を文字に変換するときに使います。 処理系によっては、TO_CHARを使って日付型などのほかのデータ型を文字に変換することができたり、 さまざまな書式設定用の引数を指定できるものもあります。 ここでは、一般的なTO_CHARの使い方を示します。
SQL> SELECT TESTNUM, TO_CHAR(TESTNUM)
FROM CONVERSIONS;
TESTNUM TO_CHAR(TESTNUM)
--------- ------------------------
95 95
23 23
68 68
3行が選択されました。
LENGTHを使って、文字列が返されていることを確かめてみます。
SQL> SELECT TESTNUM, LENGTH(TO_CHAR(TESTNUM))
FROM CONVERSIONS;
TESTNUM LENGTH(TO_CHAR(TESTNUM)) --------- ------------------------
95 2
23 2
68 2
数値に対してLENGTHを使うとエラーが発生するので、文字列が返されていることを確認できます。 TO_CHAR関数と、既に説明したCHR関数の違いに注意してください。 CHRは、使っている文字セットに応じて、数値を文字または記号に変換する関数です。
TO_NUMBER
TO_NUMBERは、TO_CHARと対になる関数で、文字列を数値に変換します。 たとえば、次のように使います。
SQL> SELECT NAME, TESTNUM, TESTNUM*TO_NUMBER(NAME)
FROM CONVERSIONS;
NAME TESTNUM TESTNUM*TO_NUMBER(NAME) --------------- -------- -----------------------
40 95 3800
13 23 299
74 68 5032
3行が選択されました。
もしTO_NUMBERが文字を返すのならエラーが発生するので、数値が返されていることを確認できます。
その他の関数
ここでは、知っておくと便利なその他の関数を3つ紹介します。
GREATESTとLEAST
GREATESTとLEASTは、一連の式の中から最大または最小の要素を返します。 たとえば、次のように使います。
SQL> SELECT GREATEST('ALPHA', 'BRAVO','FOXTROT','DELTA')
FROM CONVERSIONS;
GREATEST
-------
FOXTROT
FOXTROT
FOXTROT
3行が選択されました。
この例では、GREATESTはアルファベットの末尾に最も近い語を返しています。 また、一見不要と思われるFROMを指定していることと、FOXTROTが3回表示されていることにも注意してください。 FROMを省略すると、エラーが発生します。 SELECTには必ずFROMを指定する必要があるからです。 FOXTROTが3回表示されるのは、FROMに指定されているテーブルに3つの行が含まれており、 SELECT句で指定したGREATEST関数が行ごとに実行されるためです。
SQL> SELECT LEAST(34, 567, 3, 45, 1090)
FROM CONVERSIONS;
LEAST(34,567,3,45,1090)
-----------------------
3
3
3
この例でわかるように、GREATESTとLEASTは数値に対しても使用できます。
USER
USERは、データベースの現在のユーザーの名前を文字列で返します。
SQL> SELECT USER FROM CONVERSIONS;
USER
------------------------------
YHARAKI
YHARAKI
YHARAKI
3行が選択されました。
実際にはユーザーは YHARAKI 1人だけです。 この場合も、テーブルに含まれている行の数だけ、ユーザー名が表示されています。 USER関数は、今日の始めの方で説明した日付の関数と似ています。 USERはテーブル内の実際の列ではありませんが、テーブルに含まれている各行に対して USERを選択しています。
本書での要点
<戻る>