掲載元 2012年7/8月

テクノロジー：SQLの基礎

文字関数

リレーショナル・データベースとSQLの基本に関するシリーズ記事のパート6

このシリーズ記事のパート5"ソート順序"（Oracle Magazine、2012年5/6月）では、SQL SELECT文（問合せ）のORDER BY句の概要と、ORDER BY句を特定のオプションやキーワードとともに使用した場合の問合せ結果のデータ順序付け（ソート）に対する動作について説明しました。これで、問合せでSQL関数を使用して結果セットのデータを変換し、データベースに保存された形式とは異なる形式で表示する方法について学習を始める準備が整いました。この記事では、SQL文字関数（文字列関数またはテキスト関数とも呼ばれる）に焦点を当てて説明します。文字関数を使用すれば、文字データの表示方法を操作できます。また、このシリーズの次回以降の記事で、日付関数と数値関数について紹介します。

この記事やシリーズの今後の記事の例を試すには、Oracle Databaseインスタンスにアクセスする必要があります。必要に応じて、お使いのオペレーティング・システムに対応したOracle Databaseエディションをダウンロードし、インストールしてください。筆者がインストールをお勧めするエディションは、Oracle Database, Express Editionです。

このOracle Databaseソフトウェアをインストールする場合は、データベースの作成と構成が可能なインストール・オプションを選択してください。サンプルのユーザー・アカウントと関連する新しいスキーマを含む新しいデータベースが作成されます（SQL_101は、このシリーズ記事の例で使用するユーザー・アカウントです。また、データベースの表やその他のオブジェクトが作成されるスキーマでもあります）。インストール・プロセスの実行中にスキーマのパスワードを指定するように求められたら、SYSおよびSYSTEMのパスワードを入力して確認し、そのパスワードを覚えておいてください。

最後に、このデータベース・ソフトウェアをゼロからインストールした場合でも、既存のOracle Databaseインスタンスにアクセスする場合でも、SQLスクリプトをダウンロードして解凍し、実行して、この記事の例で必要となるSQL_101スキーマ用の表を作成します（このスクリプトをテキスト・エディタで開き、実行方法の説明を確認してください）。

綺麗な出力

ごく基本的な文字関数では、結果セット内での英数字データの書式を変更できます。たとえば、リスト1の単純な問合せでは、EMPLOYEE表にある一意の姓の値をすべて取得し、すべて大文字で表示します。LAST_NAME列にUPPER文字関数を適用することで、この操作を実行します。同様に、リスト2の問合せではLOWER文字関数を使用して、DEPARTMENT表にある部門の所在地名をすべて小文字で表示します。すべての関数は何らかの入力パラメータを受け取ります。文字関数では英数字の入力パラメータが必須となります。この入力パラメータは文字（または文字列）リテラルか、VARCHAR2、CHAR、CLOBデータ型の列のいずれかです。EMPLOYEE表のLAST_NAME列とDEPARTMENT表のLOCATION列のデータはVARCHAR2データ型として保存されています。すでに説明したとおり、リテラル文字の値は、'Smith'、'73abc'、'15-MAR-1965'など、任意の英数字の並びを一重引用符で囲んだものです。

コード・リスト1：一意の姓の値をすべて大文字で表示する問合せ

SQL> set feedback on
SQL> select distinct UPPER(last_name) "Uppercase Employee Last Name"
  2    from employee
  3  order by UPPER(last_name);

Uppercase Employee Last Name
—————————————————————————————
ECKHARDT
FRIEDLI
JAMES
LEBLANC
MICHAELS
NEWTON
PETERSON

7 rows selected.

コード・リスト2：部門の所在地をすべて小文字で表示する問合せ

SQL> select name, LOWER(location) "Lowercase Department Location"
  2    from department
  3  order by location;

NAME             Lowercase Department Location
——————————————   ——————————————————————————————
Accounting       los angeles
Payroll          new york

2 rows selected.

リスト3とリスト4は、INITCAP関数の使用例です。リスト3の問合せではINITCAPを使用して、EMPLOYEE表にすべて小文字で保存されている特定の姓と名を、1文字目のみを大文字にして表示するように変換します。リスト4の問合せで示すように、INITCAP関数は、文字列の1文字目を大文字にして、かつそれ以外の文字を小文字にします。また、この問合せでわかるように、INITCAP関数の入力パラメータでは、英数字データを保存したデータベースの列や任意の文字列に、さらに別の文字関数を指定できます。具体的には、この問合せでは特定の従業員について、EMPLOYEE表のLAST_NAME列にUPPER関数を適用しています。このUPPER関数はINITCAP関数にネストされているという言い方をします。ネストされた関数は、Oracle Databaseサーバーによって、もっとも内部にある関数から外側に向かって順に適用されます。リスト4では、UPPER関数がpetersonおよびleblancという値をPETERSONおよびLEBLANCに変換します。次に、INITCAP関数がこれらの大文字の値をPetersonおよびLeblancに変換します。

コード・リスト3：特定の名前を変換し1文字目を大文字にして表示する問合せ

SQL> set lines 32000
SQL> select first_name, last_name, INITCAP(first_name) "First Name", 
INITCAP(last_name) "Last Name"
  2    from employee
  3   where employee_id in (6569, 6570);

FIRST_NAME     LAST_NAME      First Name     Last Name
——————————     ———————————    ———————————    —————————————
michael        peterson       Michael        Peterson
mark           leblanc        Mark           Leblanc

2 rows selected.

コード・リスト4：INITCAP関数の使用例を示す問合せ

SQL> select INITCAP('eMPLOYEE lAST nAMES') "INITCAP Literal", 
INITCAP(UPPER(last_name)) "Converted Employee Last Name"
  2    from employee
  3   where employee_id in (6569, 6570);

INITCAP Literal      Converted Employee Last Name
———————————————————  —————————————————————————————
Employee Last Names  Peterson
Employee Last Names  Leblanc

2 rows selected.

結果のパディング

パディングとは、何かを追加することです。LPAD関数とRPAD関数では、任意の文字列の左側または右側に文字、空白、または記号を繰り返し追加して、文字データの結果をパディングできます。LPADは文字列の左側をパディングし、RPADは右側をパディングします。

リスト5は、RPAD関数とLPAD関数の優れた効果を示す例です。各関数は3つの入力パラメータを受け取ります。それぞれ、パディングする列の名前または文字列リテラル、文字列のパディング後の長さ、パディングに使用する文字、空白、または記号（充填文字）です。

たとえば、リスト5の問合せは、全体の長さが15文字になるまで、充填文字"."を使用して部門名の右側をパディングすることを示しています。ちょうど15文字かそれ以上の部門名の場合は、充填文字は追加されません。Accountingは10文字であるため、RPAD関数によって右側に5文字の充填文字が追加されます。この問合せでは、全体の長さが15文字になるまで所在地の左側をパディングすることも示しています。LOS ANGELESは空白を含めて11文字であるため、LPAD関数によって左側に4文字の充填文字が追加されます。

コード・リスト5：RPAD関数とLPAD関数を適用した問合せ

SQL> select RPAD(name, 15, '.') department, LPAD(location, 15, '.') location
  2    from department;

DEPARTMENT       LOCATION
———————————————  ————————————————
Accounting.........LOS ANGELES
Payroll...............NEW YORK

2 rows selected.

便利なDUAL

Oracle Databaseには、DUALという単一行、単一列の表があります。この表はさまざまな目的で役に立ちますが、その目的でも特に重要なのがOracle関数を学習することです。DUALは、SQL_101スキーマではなくSYSユーザーが所有するOracleシステム表です。パブリック・シノニムを介して、すべてのユーザーが多数のOracleシステム表を利用できるようになります。シノニムについてはこのシリーズの今後の記事で説明します。

DUAL表自体には、有用なデータは含まれていません（DUMMY列と呼ばれる1つの列で構成される1つの行があり、この列にはXという値が含まれます）。DUALは、文字列リテラルに対して動作する関数や、このシリーズの今後の記事で確認するように数値リテラルに対して動作する関数、あるいは今日の日付に対して動作する関数を試す目的で使用できます。

次のSQL文のように、DUAL表に対してSELECT文を実行すると、単一行、単一列の出力が得られます。

SQL> select *
  2    from dual;

D
-
X

1 row selected. 

現在の日付を表示するには、次のようにDUAL表を問い合わせることができます。

SQL> select sysdate
  2    from dual;

SYSDATE
——————————
18-APR-12

1 row selected.

さらに、次の例のように、DUAL表を使用して、SQL文のSELECT句内で任意の関数を試すこともできます。

SQL> select rpad('Melanie', 10, '*') Melanie, lpad('Caffrey', 10, '.')Caffrey
  2    from dual;

MELANIE     CAFFREY
——————————  ——————————
Melanie***  ...Caffrey

1 row selected.

注目すべき点は、DUALに有用なデータがなくてもこれらの関数が動作することです。上の例では、SYSDATE関数を使用してデータベースをホストするオペレーティング・システムの現在日時を表示しています。また、RPAD関数とLPAD関数を使用して筆者の名前にパディングを追加しています。

文字列の連結

結果セットの表示内にある特定の文字列を連結するとわかりやすくなる場合があります。たとえば、EMPLOYEE表のFIRST_NAMEとLAST_NAMEの値を連結できます。このタスクには、連結機能を利用できます。CONCAT関数を使用するか（リスト6を参照）、より一般的に利用される連結演算子||（2つのパイプ文字）を使用します（リスト7を参照）。

コード・リスト6：CONCAT関数の使用例を示す問合せ

SQL> select CONCAT(first_name, last_name) employee_name
  2    from employee
  3  order by employee_name;

EMPLOYEE_NAME
————————————————————————————
BetsyJames
DonaldNewton
EmilyEckhardt
FrancesNewton
MatthewMichaels
RogerFriedli
markleblanc
michaelpeterson

8 rows selected.

コード・リスト7：連結演算子||の使用例を示す問合せ

SQL> select first_name||' '||last_name employee_name
  2    from employee
  3  order by employee_name;

EMPLOYEE_NAME
———————————————————————
Betsy James
Donald Newton
Emily Eckhardt
Frances Newton
Matthew Michaels
Roger Friedli
mark leblanc
michael peterson

8 rows selected.

CONCAT関数は2つのパラメータを受け取ってそれらを連結します。また、リスト8のように複数のCONCAT関数コールをネストすることもできます。リスト7とリスト8の問合せでは、リテラル文字列と列のデータ値とを連結しています（連結演算子は入力パラメータ数に制限がなく、連結される出力が読みやすくなるため、個人的には連結演算子の方が好みです）。

コード・リスト8：ネストされたCONCATコールの使用例を示す問合せ

SQL> select CONCAT(first_name, CONCAT(' ', last_name)) employee_name
  2    from employee
  3  order by employee_name;

EMPLOYEE_NAME
————————————————————————
Betsy James
Donald Newton
Emily Eckhardt
Frances Newton
Matthew Michaels
Roger Friedli
mark leblanc
michael peterson

8 rows selected.

データのトリム

データの表示時に不要な空白や文字を削除したい場合もあります。たとえば、フォーム・アプリケーション経由で表の列に挿入されたデータには、フォームの入力フィールドでトリムされなかった余分な文字や空白が実際のデータ値の前後に含まれている可能性があります。

リスト9は、文字列値から余分な空白をトリムする問合せです。リスト9のTRIM関数は2つのパラメータを受け取ります。第1パラメータは、削除する文字、記号、または空白（一重引用符で区切る）です。第2パラメータには、トリムする文字列リテラルまたは列の値を指定します。TRIM関数では、LEADING、TRAILING、BOTHの3つのキーワードがサポートされます。リスト9の例ではTRAILINGキーワードを使用して、FIRST_NAME値の右側をトリムしています。また、LAST_NAME値に適用されたTRIM関数ではLEADINGキーワードを指定して、その値の左側の空白をトリムしています。さらに、見てのとおり、LAST_NAMEの値の右側の空白はそのまま残り、出力に含まれています。

コード・リスト9：余分な空白をトリムする問合せ

SQL> select '''' ||TRIM(TRAILING ' ' FROM 'Ashton   ') || '''' first_name,
 '''' || TRIM(LEADING ' ' FROM '  Cinder   ') || '''' last_name
  2    from dual;

FIRST_NA  LAST_NAME
————————  ——————————— 
'Ashton'  'Cinder   '

1 row selected.

リスト9の出力をリスト10の出力と比較してください。リスト10では、LAST_NAMEの値の右端にある余分な空白をトリムします。キーワードを指定しない場合のTRIM関数のデフォルト動作は、文字列の先頭と末尾の両方をトリムすることです。古いRTRIM関数とLTRIM関数も下位互換性のために利用できます。

コード・リスト10：右端の余分な空白など、余分な空白をトリムする問合せ

SQL> select '''' || TRIM(TRAILING ' ' FROM 'Ashton ') || '''' first_name,
'''' || TRIM(' Cinder ') || '''' last_name
2 from dual;

FIRST_NA LAST_NAM
———————— ————————
'Ashton' 'Cinder'

1 row selected.

文字列内の文字列の検索

類似した文字列パターン値がないか列の値を検索する必要がある場合に、INSTR文字関数を使用できます。INSTR（in stringを表す）は、ある文字列値内の部分文字列の位置を返します。リスト11の例では、EMPLOYEE表のLAST_NAME列にINSTR関数を適用して、"ton"という部分文字列が出現するすべての個所を特定しています。見てのとおり、INSTR関数は入力として、検索対象のリテラルまたは列の値と、続いて検索する部分文字列パターンを受け取ります。リスト11では、INSTR関数によって、2つの列のデータ値（両方がNewton）でのみ"ton"というパターンが見つかり、その位置として4という値が返されます。その他の値ではこの検索文字列は見つからなかったため、それらの値に関しては0と出力されます。

コード・リスト11：INSTR文字関数の使用例を示す問合せ

SQL> select last_name, INSTR(last_name, 'ton') ton_starting_point
  2    from employee
  3  order by last_name;

LAST_NAME               TON_STARTING_POINT
———————————————  —————————————————————————
Eckhardt                                 0
Friedli                                  0
James                                    0
Michaels                                 0
Newton                                   4
Newton                                   4
leblanc                                  0
peterson                                 0

8 rows selected.

さらにstarting position（開始位置）とoccurrence（出現個所）という2つのオプションのパラメータがあります。starting positionには、検索を開始する文字列内の文字位置を指定します。デフォルト動作は、1文字目、つまり文字位置1から検索を開始することです。occurrenceパラメータには、何番目に出現する部分文字列を検索するかを指定します。たとえば、Mississippiという語では"issi"という部分文字列が2個所で出現しています。このパターンの2番目の出現個所の開始位置を検索するには、INSTR関数のoccurrenceパラメータに2と指定する必要があります。

SQL> select INSTR('Mississippi', 'issi', 1, 2)
  2    from dual;

INSTR('MISSISSIPPI','ISSI',1,2)
——————————————————————————————————
                              5
1 row selected. 

文字列からの文字列の抽出

場合によっては、求める出力を得るために文字列の一部を抽出する必要があります。このタスクには、SUBSTR（substringを表す）文字関数が役に立ちます。リスト12の問合せでは、SUBSTR関数を使用してEMPLOYEE表のすべてのLAST_NAMEの値から最初の3文字を抽出します。

SUBSTR関数は、2つの必須パラメータと1つのオプション入力パラメータを受け取ります。第1パラメータは、SUBSTR関数の動作対象となるリテラルまたは列の値です。第2パラメータは部分文字列の開始文字の位置で、オプションの第3パラメータは、その部分文字列に含まれる文字数です。SUBSTR関数の第3パラメータを指定しない場合は、文字列内の残りのすべての文字が返されます。

コード・リスト12：SUBSTR文字関数の使用例を示す問合せ

SQL> select last_name, SUBSTR(last_name, 1, 3)
  2    from employee
  3  order by last_name;

LAST_NAME                       SUB
—————————————————————           ————
Eckhardt                        Eck
Friedli                         Fri
James                           Jam
Michaels                        Mic
Newton                          New
Newton                          New
leblanc                         leb
peterson                        pet

8 rows selected.

リスト13は、SUBSTR関数とINSTR関数をともに使用して、EMPLOYEE表にある、"ton"という部分文字列を含むすべてのLAST_NAME値の一部を表示する例です。この例では、INSTR関数の出力値が、SUBSTR関数の開始文字の位置を示す入力パラメータ値になっています。部分文字列の"ton"が見つからないLAST_NAME値については、次の2つの理由でLAST_NAME値全体が返されます。1つは、SUBSTRでは開始位置0が開始位置1（つまり文字列の最初の文字位置）と同様に扱われることです。もう1つは、この問合せではオプションのlengthパラメータが省略されており、残りの文字列全体が返されることです。

コード・リスト13：INSTR文字関数およびSUBSTR文字関数の使用例を示す問合せ

SQL> select last_name, INSTR(last_name, 'ton') ton_position, SUBSTR(last_name,
INSTR(last_name, 'ton')) substring_ton
2 from employee
3 order by last_name;
LAST_NAME TON_POSITION SUBSTRING_TON
————————— ———————————— —————————————————
Eckhardt   0           Eckhardt
Friedli    0           Friedli
James      0           James
Michaels   0           Michaels
Newton     4           ton
Newton     4           ton
leblanc    0           leblanc
peterson   0           peterson

8 rows selected.

サイズが重要になる場合

文字列の長さを測定する必要があることもあります。フォーム入力フィールドで入力を許可する最大文字数を測定するような場合です。リスト14の問合せでは、LENGTH関数を使用してEMPLOYEE表のすべてのFIRST_NAME値の長さを表示します。

コード・リスト14：LENGTH関数の使用例を示す問合せ

SQL> select first_name, LENGTH(first_name) length
  2    from employee
  3  order by length desc, first_name;

FIRST_NAME            LENGTH
————————————  ——————————————
Frances                    7
Matthew                    7
michael                    7
Donald                     6
Betsy                      5
Emily                      5
Roger                      5
mark                       4

8 rows selected.

文字関数は、リスト15とリスト16のように、WHERE句やORDER BY句内に配置することもできます。

リスト15では、従業員の姓と名を空白1つで区切って連結した場合の全体の長さをLENGTH関数によって計算しています。さらに、15文字を超える値のみを結果セット内に返しています。リスト16では、WHERE句内でSUBSTR関数の中にINSTR関数をネストして、姓に部分文字列"ton"が含まれる従業員のみを返し、その結果の従業員について名と姓の値を空白で区切って連結しています。最後に、この問合せのORDER BY句では、LENGTH文字関数を使用して、SELECT構文のリストで連結された姓と名の値を、文字の長さの降順でソートしています。

コード・リスト15：WHERE句での関数の使用例を示す問合せ

SQL> select first_name||' '||last_name employee_name
  2    from employee
  3   where LENGTH(first_name||' '||last_name) > 15
  4  order by employee_name;

EMPLOYEE_NAME
———————————————————
Matthew Michaels
michael peterson

2 rows selected.

コード・リスト16：WHERE句とORDER BY句での関数の使用例を示す問合せ

SQL> select first_name||' '||last_name employee_name
  2    from employee
  3   where SUBSTR(last_name, INSTR(last_name, 'ton')) = 'ton'
  4  order by LENGTH(employee_name) desc;

EMPLOYEE_NAME
———————————————————
Frances Newton
Donald Newton

2 rows selected.

結論

この記事では、SELECT文で文字関数を使用してデータの表示を操作する方法について説明しました。データ値を大文字、小文字、および大文字小文字の混在形式に変換する方法や、文字列内の文字列を検索する方法について確認しました。また、データのパディングやトリムの方法、文字列の全体の長さを指定する方法についても確認しました。この記事ではすべてのOracle文字関数を説明していません。詳しくは、こちらのドキュメントを確認してください。

このSQLの基礎に関する次回の記事では、数値関数やその他の関数について説明します。