LENGTH と INSTR ファンクションをマスター！

Oracle SQLで文字列操作を行う際に頻繁に使用する LENGTH と INSTR ファンクション。
本記事では、基本的な使い方から応用例までを段階的に解説します。初心者にもわかりやすい図解や実践タスクを盛り込んで、使い方を完全マスターしましょう！

本記事ではSCOTTサンプルスキーマを使用した例で解説します。
SCOTTサンプルスキーマは以下を実行することでインポートおよび使用が可能です。

$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？

▶ テックゴー｜IT転職エージェントの無料キャリア面談

LENGTH ファンクション

1. LENGTH とは？

LENGTH ファンクションは、指定した文字列の「文字数」を返します。
Oracleでは、マルチバイト文字も正確にカウントできるため、国際化対応のシステムでも安心して利用できます。

2. 基本構文

LENGTH(string)

引数	説明
string	長さを調べたい文字列。NULL可。

注意: string がNULLの場合、結果もNULLとなります。

3. 実践例1：従業員名の文字数を取得

以下のSQLを実行して、ENAME（従業員名）の文字数を取得します。

SELECT ENAME, LENGTH(ENAME) AS NAME_LENGTH
FROM EMP;

実行結果（例）

SQL> SELECT ENAME, LENGTH(ENAME) AS NAME_LENGTH
  2  FROM EMP;

ENAME           NAME_LENGTH
--------------- -----------
SMITH                     5
ALLEN                     5
WARD                      4
JONES                     5
MARTIN                    6
BLAKE                     5
CLARK                     5
SCOTT                     5
KING                      4
TURNER                    6
ADAMS                     5
JAMES                     5
FORD                      4
MILLER                    6

14 rows selected.

ポイント: 結果列に NAME_LENGTH という別名を付けることで、クエリの可読性を向上させています。

4. 応用例：NULL値への対応

NULL値が含まれている可能性がある場合は、NVL ファンクションを使用してエラーを防止します。

SELECT ENAME, LENGTH(NVL(ENAME, '0'))
FROM EMP;

ヒント: NULL値を「NoName」などのデフォルト値で置き換えることで、安全に処理できます。

INSTR ファンクション

1. INSTR とは？

INSTR ファンクションは、文字列内で特定の文字や部分文字列が最初または指定回目に出現する「位置」を返します。
位置は1から始まり、指定した文字列が存在しない場合は 0 を返します。

2. 基本構文

INSTR(string, substring [, start_position [, occurrence]])

引数	説明
string	検索対象の文字列。
substring	検索する文字や部分文字列。
start_position	検索を開始する位置（省略時は1）。負の値で後ろから検索。
occurrence	出現回数（省略時は1）。複数回目を指定可能。

3. 実践例1：ENAME に含まれる “A” の位置を取得

SELECT ENAME, INSTR(ENAME, 'A') AS A_POSITION
FROM EMP;

実行結果（例）

SQL> SELECT ENAME, INSTR(ENAME, 'A') AS A_POSITION
  2  FROM EMP;

ENAME           A_POSITION
--------------- ----------
SMITH                    0
ALLEN                    1
WARD                     2
JONES                    0
MARTIN                   2
BLAKE                    3
CLARK                    3
SCOTT                    0
KING                     0
TURNER                   0
ADAMS                    1
JAMES                    2
FORD                     0
MILLER                   0

14 rows selected.

4. 応用例：特定文字の2回目の出現位置を取得

2回目に出現する文字 “L” の位置を取得します。

SELECT ENAME, INSTR(ENAME, 'L', 1, 2) AS SECOND_L_POSITION
FROM EMP
WHERE ENAME = 'ALLEN';

実行結果

SQL> SELECT ENAME, INSTR(ENAME, 'L', 1, 2) AS SECOND_L_POSITION
  2  FROM EMP
  3  WHERE ENAME = 'ALLEN';

ENAME           SECOND_L_POSITION
--------------- -----------------
ALLEN                           3

5. 後ろから検索する方法

検索を後ろから行いたい場合は、start_position を負の値に設定します。

SELECT INSTR('HELLO WORLD', 'O', -1) AS LAST_O_POSITION
FROM DUAL;

結果

SQL> SELECT INSTR('HELLO WORLD', 'O', -1) AS LAST_O_POSITION
  2  FROM DUAL;

LAST_O_POSITION
---------------
              8

解説: 「O」が最後に出現する位置が返されます。

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

▶ テックゴー｜IT転職エージェントの無料キャリア面談

LENGTH と INSTR を組み合わせた応用例

実際の業務では、これらを組み合わせてデータのフィルタリングや分析を行います。

応用例1：”E” を含む従業員名を抽出

次のSQLで、ENAME に “E” が含まれる従業員名を抽出します。

SELECT ENAME,
       LENGTH(ENAME) AS NAME_LENGTH,
       INSTR(ENAME, 'E') AS E_POSITION
FROM EMP
WHERE INSTR(ENAME, 'E') > 0;

実行結果（例）

SQL> SELECT ENAME,
  2         LENGTH(ENAME) AS NAME_LENGTH,
  3         INSTR(ENAME, 'E') AS E_POSITION
  4  FROM EMP
  5  WHERE INSTR(ENAME, 'E') > 0;

ENAME           NAME_LENGTH E_POSITION
--------------- ----------- ----------
ALLEN                     5          4
JONES                     5          4
BLAKE                     5          5
TURNER                    6          5
JAMES                     5          4
MILLER                    6          5

6 rows selected.

応用例2：特定文字を含む場合の条件分岐

次のSQLでは、ENAME に “A” が含まれる従業員を「重要('important')」とマークします。

SELECT ENAME,
       CASE 
         WHEN INSTR(ENAME, 'A') > 0 THEN 'important'
         ELSE 'usually'
       END AS STATUS
FROM EMP;

実行結果（例）

SQL> SELECT ENAME,
  2         CASE
  3           WHEN INSTR(ENAME, 'A') > 0 THEN 'important'
  4           ELSE 'usually'
  5         END AS STATUS
  6  FROM EMP;

ENAME           STATUS
--------------- ---------
SMITH           usually
ALLEN           important
WARD            important
JONES           usually
MARTIN          important
BLAKE           important
CLARK           important
SCOTT           usually
KING            usually
TURNER          usually
ADAMS           important
JAMES           important
FORD            usually
MILLER          usually

14 rows selected.

応用例3：最も長い従業員名を取得

従業員名の中で最も文字数が多い名前を取得します。

SELECT ENAME, LENGTH(ENAME) AS NAME_LENGTH
FROM EMP
WHERE LENGTH(ENAME) = (SELECT MAX(LENGTH(ENAME)) FROM EMP);

実践タスク

以下のタスクに挑戦してみましょう。

ENAME に “R” が含まれる従業員を抽出するSQLを書いてください。
各従業員名の長さを取得し、名前の長さで降順にソートしてください。
ENAME の中で文字 “O” が2回以上出現する従業員を抽出してください。

まとめ

LENGTH は文字列の長さを取得。
INSTR は文字列内で特定の文字や部分文字列の位置を調べる。
これらを組み合わせることで、文字列データの分析や操作を効率化できる。

本記事のポイント

コードサンプルに加えて具体的な実行結果を示すことで、実践的な理解を促進。
応用例では、現場での活用を想定したシナリオを提案。
実践タスクを盛り込み、学習効果を高める仕組みを採用。

これで、初心者でも LENGTH と INSTR ファンクションを自信を持って使いこなせるはずです。どんどん試してみましょう！

[参考]
Oracle Database SQL言語リファレンス 19c

💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？

▶ テックゴー｜IT転職エージェントの無料キャリア面談