落とし穴はこれで回避！ Oracle DB 12c SQL基礎弱点克服スクール｜第12回（最終回）

12c SQL基礎の総復習「模擬問題」

2017/02/22 14:00

単一行関数の使用による出力のカスタマイズ

問４

EMPLOYEES表のデータを確認してください。

FIRST_NAME      LAST_NAME
--------------- ---------------
Alexis          Bull
Lex             De Haan
Tayler          Fox
Alexander       Hunold
Alexander       Khoo
Jose Manuel     Urman

名前が3つの単語で構成されている社員を抽出し、次のように3つ目の単語の部分を「*」で置き換えて出力しようと考えています。

Lex De ****
Jose Manuel *****

必要な出力を得るためには、どの問い合わせを使用しますか。最適なものを選択しなさい。

a. SELECT RPAD(
SUBSTR(CONCAT(CONCAT(first_name,' '),last_name)
,1,INSTR(CONCAT(CONCAT(first_name,' '),last_name),' ',1,2))
,LENGTH(CONCAT(CONCAT(first_name,' '),last_name))
,'*')
FROM employees
WHERE INSTR(CONCAT(first_name,last_name),' ') <> 0;
b. SELECT RPAD(
SUBSTR(CONCAT(CONCAT(first_name,' '),last_name)
,-1,INSTR(CONCAT(CONCAT(first_name,' '),last_name),' ',1,2))
,LENGTH(CONCAT(CONCAT(first_name,' '),last_name))
,'*')
FROM employees
WHERE INSTR(CONCAT(first_name,last_name),' ',-1,1) >= 1;
c. SELECT RPAD(
SUBSTR(CONCAT(CONCAT(first_name,' '),last_name)
,1,INSTR(CONCAT(CONCAT(first_name,' '),last_name),' ',-1,2))
,LENGTH(CONCAT(CONCAT(first_name,' '),last_name))
,'*')
FROM employees
WHERE INSTR(CONCAT(first_name,last_name),' ',-1,1) >= 1;
d. SELECT RPAD(
SUBSTR(CONCAT(CONCAT(first_name,' '),last_name)
,1,INSTR(CONCAT(CONCAT(first_name,' '),last_name),' ',1,2))
,LENGTH(CONCAT(CONCAT(first_name,' '),last_name))
,'*')
FROM employees
WHERE INSTR(CONCAT(first_name,last_name),' ') = 0;

次の実行例が示すとおり、FIRST_NAMEとLAST_NAMEをくっつけると、3つの単語から構成されている名前は、空白がどこかに1つ存在することになります。

SQL> select concat(first_name,last_name)
  2  from employees;

CONCAT(FIRST_NAME,LAST_NAME)
------------------------------
AlexisBull
LexDe Haan
TaylerFox
AlexanderHunold
AlexanderKhoo
Jose ManuelUrman

INSTR関数は次のように4つの引数を受け取り、指定した文字の位置を戻します。

構文 INSTR関数の構文

INSTR(対象文字列,検索文字列,検索開始位置,出現順番)

3番目と4番目の引数は省略可能です。省略した場合先頭から探し始めて最初に見つけた空白の位置を戻します。3番目の引数がマイナスの場合、後ろから探し始めます。したがって、WHERE INSTR(CONCAT(first_name,last_name),' ')<> 0（選択肢a）と、WHERE INSTR(CONCAT(first_name,last_name),' ',-1,1) >= 1（選択肢b、c）は、名前が3つの単語で構成されている社員を抽出できます（選択肢dは不正解）。

SQL> -- 選択肢a
SQL> SELECT CONCAT(CONCAT(first_name,' '),last_name)
  2  FROM employees
  3  WHERE INSTR(CONCAT(first_name,last_name),' ') <> 0;

CONCAT(CONCAT(FIRST_NAME,''),LAST_NAME)
-----------------------------------------
Lex De Haan
Jose Manuel Urman

SQL> -- 選択肢b、選択肢c
SQL> SELECT CONCAT(CONCAT(first_name,' '),last_name)
  2  FROM employees
  3  WHERE INSTR(CONCAT(first_name,last_name),' ',-1,1) >= 1;

CONCAT(CONCAT(FIRST_NAME,''),LAST_NAME)
-----------------------------------------
Lex De Haan
Jose Manuel Urman

次に、3つ目の単語の部分を「*」で置き換えている選択肢を求めるわけですが、これが難解です。「*」で置き換えるためには、LPAD関数またはRPAD関数を使用すればよいのですが、3つ目ということは最後の単語ですから、右から置き換えるRPAD関数がよさそうだと想像できるでしょう。

構文 RPAD関数の構文

RPAD(対象文字列,全体の桁数,埋め込み文字列)

埋め込み文字列は「*」で、全体の桁数は社員の氏名（FIRST_NAMEとLAST_NAMEを空白を挟んでくっつけたもの）です。全体の桁数は、次のとおりLENGTH関数で求められます。なお、CONCAT関数は引数は2つしか指定できないので、FIRST_NAMEとLAST_NAMEの間に空白を挟みたければ、CONCAT関数を入れ子にしなければいけません。

SQL> SELECT LENGTH(CONCAT(CONCAT(first_name,' '),last_name))
  2  FROM employees
  3  WHERE INSTR(CONCAT(first_name,last_name),' ',-1,1) >= 1;

LENGTH(CONCAT(CONCAT(FIRST_NAME,''),LAST_NAME))
-----------------------------------------------
                                             11
                                             17

対象文字列は3つ目の単語の前。言い換えると、2つ目の空白以前の文字列です。これはSUBSTR関数を使用して、先頭から2つ目の空白の位置までの文字列を抜き出してしまえばいいですね。

構文 SUBSTR関数の構文

SUBSTR(対象文字列,抜き出し開始文字位置,抜き出す文字数)

SUBSTR関数を使って、先頭から2つ目の空白の位置までの文字列を抜き出きだしてみます。

SQL> SELECT SUBSTR(CONCAT(CONCAT(first_name,' '),last_name)
  2         ,1,INSTR(CONCAT(CONCAT(first_name,' '),last_name),' ',1,2))
  3  FROM employees
  4  WHERE INSTR(CONCAT(first_name,last_name),' ',-1,1) >= 1;

SUBSTR(CONCAT(CONCAT(FIRST_NAME,''),LAST_NAME),1,INSTR(CONCAT(CONCAT(FIRST_NAME,
--------------------------------------------------------------------------------
Lex De
Jose Manuel

ここまで説明した条件をすべて満たしていた選択肢aを実行した結果は次のとおりです（選択肢aは正解、選択肢b、cは不正解）

SQL> --選択肢a
SQL> SELECT RPAD(
  2           SUBSTR(CONCAT(CONCAT(first_name,' '),last_name)
  3           ,1,INSTR(CONCAT(CONCAT(first_name,' '),last_name),' ',1,2))
  4          ,LENGTH(CONCAT(CONCAT(first_name,' '),last_name))
  5          ,'*')
  6  FROM employees
  7  WHERE INSTR(CONCAT(first_name,last_name),' ')<> 0;

RPAD(SUBSTR(CONCAT(CONCAT(FIRST_NAME,''),LAST_NAME),1,INSTR(CONCAT(CONCAT(FIRST_
--------------------------------------------------------------------------------
Lex De ****
Jose Manuel *****

SQL> --選択肢b
SQL> SELECT RPAD(
  2           SUBSTR(CONCAT(CONCAT(first_name,' '),last_name)
  3           ,-1,INSTR(CONCAT(CONCAT(first_name,' '),last_name),' ',1,2))
  4          ,LENGTH(CONCAT(CONCAT(first_name,' '),last_name))
  5          ,'*')
  6  FROM employees
  7  WHERE INSTR(CONCAT(first_name,last_name),' ',-1,1) >= 1;

RPAD(SUBSTR(CONCAT(CONCAT(FIRST_NAME,''),LAST_NAME),-1,INSTR(CONCAT(CONCAT(FIRST
--------------------------------------------------------------------------------
n**********
n****************

SQL> --選択肢c
SQL> SELECT RPAD(
  2           SUBSTR(CONCAT(CONCAT(first_name,' '),last_name)
  3           ,1,INSTR(CONCAT(CONCAT(first_name,' '),last_name),' ',-1,2))
  4          ,LENGTH(CONCAT(CONCAT(first_name,' '),last_name))
  5          ,'*')
  6  FROM employees
  7  WHERE INSTR(CONCAT(first_name,last_name),' ',-1,1) >= 1;

RPAD(SUBSTR(CONCAT(CONCAT(FIRST_NAME,''),LAST_NAME),1,INSTR(CONCAT(CONCAT(FIRST_
--------------------------------------------------------------------------------
Lex *******
Jose ************

関数は第3回などで解説しましたが、ここでも「LPAD関数、RPAD関数も注意すべき関数です」と言い残したので、今回問題を用意しました。実際の試験でも、こういう関数の入れ子の出題は多いです。

しかし、解説している私の頭がこんがらがってくるほどですから、解いている皆さんは途中で投げ出したくなったことでしょう。出題する側にとって、引数が省略できる関数、マイナス値が指定できる関数は問題を作りやすいので注意してください。

次のページ
変換関数と条件式の使用

この記事は参考になりましたか？

印刷用を表示

めざせオラクルマスターホルダー！ Oracle DB 12c SQL基礎弱点克服スクール連載記事一覧: 12c SQL基礎の総復習「模擬問題」

リレーショナルデータベースの概要～正規化とER図

データ定義言語（DDL）の概要

もっと読む

この記事の著者: 林優子（ハヤシユウコ）

日本オラクル株式会社の教育ビジネスのスタートアップを全面的に支援し、バージョン5の頃からOracleに携わるベテラン講師として知る人も多い。Oracle認定講師を表彰するExcellent Instructorを連続受賞。1ランク上のITスペシャリスト育成を目標に、データベース分野にとどまらず「プレゼンテーション」、「ロジカルシンキング」などのトレーニングも手がけている。著書に『オラクルマスター教科書』シリーズ（翔泳社）、『プロとしてのデータモデリング入門』（SBクリエイティブ）など。その他、雑誌執筆、著書・メディア出演も多数。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この著者の最近の執筆記事