Oracleデータベースを使いこなすために欠かせない文字列操作ファンクションの中で、CONCATとSUBSTRは非常に便利です。本記事では、この2つのファンクションの基本から応用までを、scottスキーマを使用して実例を交えながら徹底解説します。
本記事ではSCOTTサンプルスキーマを使用した例で解説します。
SCOTTサンプルスキーマは以下を実行することでインポートおよび使用が可能です。
$ sqlplus / as sysdba
SQL> @?/rdbms/admin/utlsampl.sql
$ sqlplus scott/tiger
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
1. CONCATファンクション：2つの文字列を結合する
構文と基本的な動作
CONCATファンクションは2つの文字列を結合します。
CONCAT(string1, string2)
- string1：結合したい最初の文字列
- string2：結合したい2番目の文字列
実例：従業員名と職種を結合
scottスキーマのEMP表を使い、従業員名（ENAME）と職種（JOB）を結合して表示します。
SELECT CONCAT(ENAME, ' (' || JOB || ')') AS EMP_INFO
FROM EMP;
実行結果
SQL> SELECT CONCAT(ENAME, ' (' || JOB || ')') AS EMP_INFO
2 FROM EMP;
EMP_INFO
----------------------
SMITH (CLERK)
ALLEN (SALESMAN)
WARD (SALESMAN)
JONES (MANAGER)
MARTIN (SALESMAN)
BLAKE (MANAGER)
CLARK (MANAGER)
SCOTT (ANALYST)
KING (PRESIDENT)
TURNER (SALESMAN)
ADAMS (CLERK)
JAMES (CLERK)
FORD (ANALYST)
MILLER (CLERK)
14 rows selected.
ポイント
- 柔軟性の限界：
CONCATは2つの文字列しか結合できません。それ以上の文字列を結合するには||演算子を使用します。sqlコードをコピーするSELECT ENAME || ' - ' || JOB || ' (' || DEPTNO || ')' AS FULL_INFO FROM EMP;
2. SUBSTRファンクション：文字列の一部を抽出する
構文と基本的な動作
SUBSTRファンクションは、指定した位置から文字列の一部を抽出します。
SUBSTR(string, start_position, length)
- string：対象となる文字列
- start_position：抽出開始位置（1から始まります）
- length（省略可）：抽出する文字数
実例：従業員名の最初の3文字を取得
従業員名（ENAME）の最初の3文字を抽出します。
SELECT ENAME, SUBSTR(ENAME, 1, 3) AS SHORT_NAME
FROM EMP;
実行結果
SQL> SELECT ENAME, SUBSTR(ENAME, 1, 3) AS SHORT_NAME
2 FROM EMP;
ENAME SHORT_NAME
--------------- ------------
SMITH SMI
ALLEN ALL
WARD WAR
JONES JON
MARTIN MAR
BLAKE BLA
CLARK CLA
SCOTT SCO
KING KIN
TURNER TUR
ADAMS ADA
JAMES JAM
FORD FOR
MILLER MIL
14 rows selected.
3. CONCATとSUBSTRを組み合わせる応用例
応用例1：名前の一部と職種を結合
従業員名の最初の3文字と職種を結合して表示します。
SELECT CONCAT(SUBSTR(ENAME, 1, 3), ' - ' || JOB) AS CUSTOM_INFO
FROM EMP;
実行結果
SQL> SELECT CONCAT(SUBSTR(ENAME, 1, 3), ' - ' || JOB) AS CUSTOM_INFO
2 FROM EMP;
CUSTOM_INFO
------------------------
SMI - CLERK
ALL - SALESMAN
WAR - SALESMAN
JON - MANAGER
MAR - SALESMAN
BLA - MANAGER
CLA - MANAGER
SCO - ANALYST
KIN - PRESIDENT
TUR - SALESMAN
ADA - CLERK
JAM - CLERK
FOR - ANALYST
MIL - CLERK
14 rows selected.
応用例2：部門番号付きのカスタム表示
従業員名の最初の3文字、職種、部門番号をカスタマイズした形で表示します。
SELECT ENAME, JOB, DEPTNO,
SUBSTR(ENAME, 1, 3) || ' (' || JOB || ') - DEPT ' || DEPTNO AS FULL_INFO
FROM EMP;
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
実行結果
SQL> SELECT ENAME, JOB, DEPTNO,
2 SUBSTR(ENAME, 1, 3) || ' (' || JOB || ') - DEPT ' || DEPTNO AS FULL_INFO
3 FROM EMP;
ENAME JOB DEPTNO FULL_INFO
--------------- --------- ---------- --------------------------------------
SMITH CLERK 20 SMI (CLERK) - DEPT 20
ALLEN SALESMAN 30 ALL (SALESMAN) - DEPT 30
WARD SALESMAN 30 WAR (SALESMAN) - DEPT 30
JONES MANAGER 20 JON (MANAGER) - DEPT 20
MARTIN SALESMAN 30 MAR (SALESMAN) - DEPT 30
BLAKE MANAGER 30 BLA (MANAGER) - DEPT 30
CLARK MANAGER 10 CLA (MANAGER) - DEPT 10
SCOTT ANALYST 20 SCO (ANALYST) - DEPT 20
KING PRESIDENT 10 KIN (PRESIDENT) - DEPT 10
TURNER SALESMAN 30 TUR (SALESMAN) - DEPT 30
ADAMS CLERK 20 ADA (CLERK) - DEPT 20
JAMES CLERK 30 JAM (CLERK) - DEPT 30
FORD ANALYST 20 FOR (ANALYST) - DEPT 20
MILLER CLERK 10 MIL (CLERK) - DEPT 10
14 rows selected.
4. 実務に役立つポイントと注意点
CONCATの代替：||演算子
複数の文字列を結合する際、||演算子を使う方が柔軟で効率的です。以下のように使用できます：
SELECT ENAME || ' - ' || JOB || ' (' || DEPTNO || ')' AS CONCATENATED_STRING
FROM EMP;
実行結果
SQL> SELECT ENAME || ' - ' || JOB || ' (' || DEPTNO || ')' AS CONCATENATED_STRING
2 FROM EMP;
CONCATENATED_STRING
-----------------------------------------------------------------
SMITH - CLERK (20)
ALLEN - SALESMAN (30)
WARD - SALESMAN (30)
JONES - MANAGER (20)
MARTIN - SALESMAN (30)
BLAKE - MANAGER (30)
CLARK - MANAGER (10)
SCOTT - ANALYST (20)
KING - PRESIDENT (10)
TURNER - SALESMAN (30)
ADAMS - CLERK (20)
JAMES - CLERK (30)
FORD - ANALYST (20)
MILLER - CLERK (10)
14 rows selected.
SUBSTRの柔軟性：負の開始位置
開始位置を負の値にすると、文字列の末尾からカウントされます。
例：
SELECT SUBSTR('ORACLE', -3, 2) AS RESULT FROM DUAL;
実行結果
SQL> SELECT SUBSTR('ORACLE', -3, 2) AS RESULT FROM DUAL;
RE
--
CL
5. 練習課題：自分で試してみよう
- 従業員名と部門名を結合
scottスキーマのEMP表とDEPT表を結合し、従業員名と部門名を結合して表示してください。SELECT CONCAT(ENAME, ' - ' || DNAME) AS EMP_DEPT_INFO FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO; - 従業員名の中間部分を抽出
従業員名の2文字目から4文字目を抽出してください。SELECT ENAME, SUBSTR(ENAME, 2, 3) AS MID_NAME FROM EMP;
まとめ
- CONCATファンクションは2つの文字列を結合する際に使用します。ただし、柔軟性に限界があるため
||演算子の使用も検討しましょう。 - SUBSTRファンクションは文字列の一部を抽出でき、カスタム表示やデータ解析に非常に役立ちます。
- 実務では、これらを組み合わせてデータ表示を柔軟にカスタマイズできます。
Oracleデータベースの強力な文字列操作機能を活用し、データ処理のスキルをさらに磨きましょう！
[参考]
Oracle Database SQL言語リファレンス 19c
💰 【PR】Oracleエンジニアの市場価値、調べてみませんか？
Oracleのスキルは需要が高く、特定の資格や経験を持っていると年収が大幅にアップするケースがあります。まずはIT専門のエージェントで非公開求人をチェックしてみませんか？
コメント