文字列の問題
問２
あなたは、EMP表の中で、最後から2文字目がEで、なおかつ名前の中にAという文字を含む従業員を出力する必要があります。必要な出力を得るためには、どの問合せを使用しますか
-
a. SELECT ename FROM emp
WHERE INSTR(ename,-2,1)= 'E'
AND SUBSTR(ename,'A')!=0; -
b. SELECT ename FROM emp
WHERE SUBSTR(ename,-2,1)= 'E'
AND INSTR(ename,'A')<>0; -
c. SELECT ename FROM emp
WHERE SUBSTR(ename,-2,1)= 'E'
AND INSTR(ename,'A') IS NOT NULL; -
d. SELECT ename FROM emp
WHERE INSTR(ename,'E',-2,1)=LENGTH(ename)-1
AND INSTR(ename,'A')<>0; -
e. SELECT ename FROM emp
WHERE INSTR(ename,'E',-2,1)<>0
AND SUBSTR(ename,LENGTH(ename),1)='A'; -
f. SELECT ename FROM emp
WHERE INSTR(ename,'E',2,1)=LENGTH(ename)-1
AND INSTR(ename,'A')<>0;
INSTR関数とSUBSTR関数を混乱せずに使い分けられましたか？ 「最後から2文字目がE」かどうかは、SUBSTR関数、INSTR関数のどちらでも求めることができるでしょう。 SUBSTR関数を使用する場合、「指定した位置（最後から2文字目）から、指定した文字数分（1文字分）の文字を返し」、それが「E」かどうかで判断すればよいので、次のようになります。
SQL> SELECT ename FROM emp 2 WHERE SUBSTR(ename,-2,1)= 'E'; ENAME ---------- ALLEN JONES TURNER JAMES MILLER
2番目の引数である検索開始位置は、正数を与えれば先頭からの位置、負数を与えれば末尾からの位置を示すので、最後から2番目であれば「-2」とすればよいです。
一方、INSTR関数を使用する場合には、「結果として返される、指定した文字列（E）の位置」が最後から2番目であればよいとなります。 ところが、enameの長さは人それぞれですから、最後から2番目が先頭から何番目かは定まりません。名前が5文字のとき、最後から2番目は先頭から4番目ですし、名前が6文字のとき、最後から2番目は先頭から5番目です。
そこで、LENGTH関数を使用して、名前の文字数より1文字少ない値と一致していれば、最後から2文字目だと判断します。 このとき、先頭から検索しようとも末尾から検索しようとも、INSTR関数が返す文字の位置は、先頭から数えた位置であることに注意しましょう。
SQL> SELECT ename, LENGTH(ename), INSTR(ename,'E',2,1), INSTR(ename,'E',-2,1) 2 FROM emp 3 WHERE SUBSTR(ename,-2,1)= 'E'; ENAME LENGTH(ENAME) INSTR(ENAME,'E',2,1) INSTR(ENAME,'E',-2,1) ---------- ------------- -------------------- --------------------- ALLEN 5 4 4 JONES 5 4 4 TURNER 6 5 5 JAMES 5 4 4 MILLER 6 5 5
「Aという文字を含む」という条件に関しては、INSTR関数を使用してAの位置を求めればよいでしょう。Aが存在しなければ0が返ってきますので、0以外であるかどうかでAを含む／含まないを判断できます。
SQL> SELECT ename, INSTR(ename,'A') 2 FROM emp 3 WHERE SUBSTR(ename,-2,1)= 'E'; ENAME INSTR(ENAME,'A') ---------- ---------------- ALLEN 1 JONES 0 TURNER 0 JAMES 2 MILLER 0
正解は、次の実行例のとおり、選択肢b、d、fです。
SQL> -- a
SQL> SELECT ename FROM emp
2 WHERE INSTR(ename,-2,1)= 'E'
3 AND SUBSTR(ename,'A')!=0;
WHERE INSTR(ename,-2,1)= 'E'
*
行2でエラーが発生しました。:
ORA-01722: 数値が無効です。
SQL>
-- b
SELECT ename FROM emp
WHERE SUBSTR(ename,-2,1)= 'E'
AND INSTR(ename,'A')<>0;
ENAME
----------
ALLEN
JAMES
SQL> -- c
SQL> SELECT ename FROM emp
2 WHERE SUBSTR(ename,-2,1)= 'E'
3 AND INSTR(ename,'A') IS NOT NULL;
ENAME
----------
ALLEN
JONES
TURNER
JAMES
MILLER
SQL> -- d
SQL> SELECT ename FROM emp
2 WHERE INSTR(ename,'E',-2,1)=LENGTH(ename)-1
3 AND INSTR(ename,'A')<>0;
ENAME
----------
ALLEN
JAMES
SQL> -- e
SQL> SELECT ename FROM emp
2 WHERE INSTR(ename,'E',-2,1)<>0
3 AND SUBSTR(ename,LENGTH(ename),1)='A';
レコードが選択されませんでした。
SQL> -- f
SQL> SELECT ename FROM emp
2 WHERE INSTR(ename,'E',2,1)=LENGTH(ename)-1
3 AND INSTR(ename,'A')<>0;
ENAME
----------
ALLEN
JAMES
文字列関数は、関数以外のカテゴリでも選択肢の中に登場します。先ほどの例題には出てきませんでしたが、LPAD関数、RPAD関数も注意すべき関数です。確認しておいてください。
次は日付関数を扱う例題です。
