単一行関数の使用による出力のカスタマイズ
問４
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(対象文字列,検索文字列,検索開始位置,出現順番)
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(対象文字列,全体の桁数,埋め込み文字列)
埋め込み文字列は「*」で、全体の桁数は社員の氏名（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関数を使って、先頭から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関数も注意すべき関数です」と言い残したので、今回問題を用意しました。実際の試験でも、こういう関数の入れ子の出題は多いです。
しかし、解説している私の頭がこんがらがってくるほどですから、解いている皆さんは途中で投げ出したくなったことでしょう。出題する側にとって、引数が省略できる関数、マイナス値が指定できる関数は問題を作りやすいので注意してください。
