Oracle SQLで文字列操作を行う際に頻繁に使用する LENGTH と INSTR ファンクション。
本記事では、基本的な使い方から応用例までを段階的に解説します。初心者にもわかりやすい図解や実践タスクを盛り込んで、使い方を完全マスターしましょう！
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;
実行結果（例）
| ENAME | NAME_LENGTH |
|---|---|
| SMITH | 5 |
| ALLEN | 5 |
| WARD | 4 |
| JONES | 5 |
ポイント: 結果列に
NAME_LENGTHという別名を付けることで、クエリの可読性を向上させています。
4. 応用例：NULL値への対応
NULL値が含まれている可能性がある場合は、NVL ファンクションを使用してエラーを防止します。
SELECT ENAME, LENGTH(NVL(ENAME, 'NoName')) AS NAME_LENGTH
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;
実行結果（例）
| ENAME | A_POSITION |
|---|---|
| SMITH | 0 |
| ALLEN | 2 |
| WARD | 1 |
| JONES | 0 |
4. 応用例：特定文字の2回目の出現位置を取得
2回目に出現する文字 “L” の位置を取得します。
SELECT ENAME, INSTR(ENAME, 'L', 1, 2) AS SECOND_L_POSITION
FROM EMP
WHERE ENAME = 'ALLEN';
実行結果
| ENAME | SECOND_L_POSITION |
|---|---|
| ALLEN | 3 |
5. 後ろから検索する方法
検索を後ろから行いたい場合は、start_position を負の値に設定します。
SELECT INSTR('HELLO WORLD', 'O', -1) AS LAST_O_POSITION
FROM DUAL;
結果
| LAST_O_POSITION |
|---|
| 8 |
解説: 「O」が最後に出現する位置が返されます。
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;
実行結果（例）
| ENAME | NAME_LENGTH | E_POSITION |
|---|---|---|
| JONES | 5 | 3 |
| BLAKE | 5 | 4 |
| CLARK | 5 | 3 |
応用例2：特定文字を含む場合の条件分岐
次のSQLでは、ENAME に “A” が含まれる従業員を「重要」とマークします。
SELECT ENAME,
CASE
WHEN INSTR(ENAME, 'A') > 0 THEN '重要'
ELSE '通常'
END AS STATUS
FROM EMP;
実行結果（例）
| ENAME | STATUS |
|---|---|
| ALLEN | 重要 |
| WARD | 重要 |
| SMITH | 通常 |
応用例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
コメント