【Oracle】SUBSTR()関数で文字列の一部を切り出す方法｜負の値・SUBSTRB・実務パターンも解説

SUBSTR(文字列, 開始位置 [, 文字数])

ポイント：Oracleの文字位置は 1始まり です。プログラミング言語のように0始まりではないので注意してください。ただし、0を指定しても1と同じ動作をします。

-- 3文字目から3文字分を切り出す
SELECT SUBSTR('Hello World', 3, 3) AS result FROM dual;

実行結果

RESULT
------
llo

-- 先頭から5文字を取得
SELECT SUBSTR('Oracle Database', 1, 5) AS result FROM dual;

実行結果

RESULT
------
Oracl

-- 8文字目から末尾まで取得
SELECT SUBSTR('Oracle Database', 8) AS result FROM dual;

実行結果

RESULT
--------
Database

-- 末尾から5文字目を起点に3文字分を切り出す
SELECT SUBSTR('Hello World', -5, 3) AS result FROM dual;

-- 末尾から3文字を取得（文字数省略）
SELECT SUBSTR('Hello World', -3) AS result FROM dual;

実行結果

-- 1つ目のクエリ
RESULT
------
Wor

-- 2つ目のクエリ
RESULT
------
rld

開始位置の数え方まとめ

• 正の値：先頭から数える（1 = 先頭文字）
• 負の値：末尾から数える（-1 = 最後の文字）
• 0：1と同じ扱い（先頭文字から）

SELECT
  SUBSTR('ABCDEFGHIJ', -1)     AS last_1,      -- J
  SUBSTR('ABCDEFGHIJ', -3)     AS last_3,      -- HIJ
  SUBSTR('ABCDEFGHIJ', -5, 2) AS mid_2,       -- FG
  SUBSTR('ABCDEFGHIJ', -7, 4) AS mid_4        -- DEFG
FROM dual;

実行結果

LAST_1  LAST_3  MID_2  MID_4
------  ------  -----  -----
J       HIJ     FG     DEFG

-- 開始位置0 と 開始位置1 の比較
SELECT
  SUBSTR('ABCDE', 0, 3) AS pos_0,   -- ABC
  SUBSTR('ABCDE', 1, 3) AS pos_1    -- ABC
FROM dual;

実行結果

POS_0  POS_1
-----  -----
ABC    ABC

注意：Oracle では開始位置 0 は 1 と同じ動作ですが、他のRDBMSでは異なる場合があります。可読性のためにも、先頭から切り出す場合は 1 を明示することを推奨します。

-- 従業員名の先頭2文字をイニシャルとして取得
SELECT
  emp_name,
  SUBSTR(emp_name, 1, 2) AS initial
FROM employees;

実行結果

EMP_NAME    INITIAL
----------  -------
田中太郎    田中
鈴木花子    鈴木
Smith       Sm

SUBSTRB(文字列, 開始バイト位置 [, バイト数])

-- 文字単位（SUBSTR）
SELECT SUBSTR('あいうえお', 1, 3) AS char_result FROM dual;

-- バイト単位（SUBSTRB）
SELECT SUBSTRB('あいうえお', 1, 3) AS byte_result FROM dual;

実行結果

-- SUBSTR（文字単位）
CHAR_RESULT
-----------
あいう

-- SUBSTRB（バイト単位）
BYTE_RESULT
-----------
あ

注意：SUBSTRBでマルチバイト文字の途中のバイトを指定すると、文字化けや空白が返される場合があります。バイト境界を意識して指定してください。

-- 固定長レコード（100バイト）から各フィールドを抽出
SELECT
  SUBSTRB(record_data, 1,  10) AS customer_code,   -- 1〜10バイト
  SUBSTRB(record_data, 11, 30) AS customer_name,   -- 11〜40バイト
  SUBSTRB(record_data, 41, 15) AS phone_number     -- 41〜55バイト
FROM import_records;

-- 社員番号の先頭2文字が「AB」の社員を取得
SELECT emp_id, emp_name
FROM employees
WHERE SUBSTR(emp_id, 1, 2) = 'AB';

注意：WHERE句でSUBSTR()を使うとインデックスが効きません（関数インデックスを作成している場合を除く）。前方一致であれば LIKE 'AB%' のほうがパフォーマンスが良い場合が多いです。

-- ファイル名の末尾4文字が「.csv」のレコードを取得
SELECT file_name
FROM file_list
WHERE SUBSTR(file_name, -4) = '.csv';

-- メールアドレスからユーザー名とドメインを分離
SELECT
  email,
  SUBSTR(email, 1, INSTR(email, '@') - 1) AS user_name,
  SUBSTR(email, INSTR(email, '@') + 1)    AS domain
FROM employees;

実行結果

EMAIL                 USER_NAME    DOMAIN
--------------------  -----------  -----------
tanaka@example.com    tanaka       example.com
suzuki@company.co.jp  suzuki       company.co.jp

ポイント：INSTR(email, '@') は @ の位置を返します。その位置の前後でSUBSTRの範囲を調整することで、区切り文字を含まない形で分割できます。

-- ファイルパスから最後の「/」以降（ファイル名）を取得
SELECT
  file_path,
  SUBSTR(file_path, INSTR(file_path, '/', -1) + 1) AS file_name
FROM file_list;

実行結果

FILE_PATH                    FILE_NAME
---------------------------  ----------
/home/user/docs/report.pdf   report.pdf
/var/log/app/error.log       error.log

-- ファイル名から拡張子を取得
SELECT
  file_name,
  SUBSTR(file_name, INSTR(file_name, '.', -1) + 1) AS extension
FROM file_list;

実行結果

FILE_NAME      EXTENSION
-------------  ---------
report.pdf     pdf
data.csv       csv
image.jpeg     jpeg

-- 電話番号「03-1234-5678」を分解
SELECT
  phone,
  SUBSTR(phone, 1, INSTR(phone, '-') - 1)  AS area_code,
  SUBSTR(phone,
    INSTR(phone, '-') + 1,
    INSTR(phone, '-', 1, 2) - INSTR(phone, '-') - 1
  ) AS local_code,
  SUBSTR(phone, INSTR(phone, '-', 1, 2) + 1) AS subscriber
FROM customers;

実行結果

PHONE         AREA_CODE  LOCAL_CODE  SUBSCRIBER
------------  ---------  ----------  ----------
03-1234-5678  03         1234        5678
06-9876-5432  06         9876        5432

-- 郵便番号「123-4567」を前半3桁・後半4桁に分割
SELECT
  postal_code,
  SUBSTR(postal_code, 1, 3) AS area,
  SUBSTR(postal_code, 5, 4) AS detail
FROM addresses
WHERE postal_code LIKE '___-____';

実行結果

POSTAL_CODE  AREA  DETAIL
-----------  ----  ------
100-0001     100   0001
530-0001     530   0001

-- 「20260305」形式の日付文字列を分解
SELECT
  date_str,
  SUBSTR(date_str, 1, 4) AS yyyy,
  SUBSTR(date_str, 5, 2) AS mm,
  SUBSTR(date_str, 7, 2) AS dd
FROM (
  SELECT '20260305' AS date_str FROM dual
);

実行結果

DATE_STR  YYYY  MM  DD
--------  ----  --  --
20260305  2026  03  05

-- 名前の先頭1文字だけ表示し、残りを「***」でマスク
SELECT
  emp_name,
  SUBSTR(emp_name, 1, 1) || '***' AS masked_name
FROM employees;

-- クレジットカード番号の末尾4桁のみ表示
SELECT
  '****-****-****-' || SUBSTR(card_number, -4) AS masked_card
FROM payments;

実行結果

EMP_NAME  MASKED_NAME
--------  -----------
田中太郎  田***
鈴木花子  鈴***

MASKED_CARD
-------------------
****-****-****-1234
****-****-****-5678

-- 「ABC-123-XYZ」から数字部分を取得

-- SUBSTR + INSTR の場合
SELECT SUBSTR('ABC-123-XYZ',
  INSTR('ABC-123-XYZ', '-') + 1,
  INSTR('ABC-123-XYZ', '-', 1, 2) - INSTR('ABC-123-XYZ', '-') - 1
) AS result1 FROM dual;

-- REGEXP_SUBSTR の場合
SELECT REGEXP_SUBSTR('ABC-123-XYZ', '\d+') AS result2 FROM dual;

実行結果（両方同じ）

RESULT
------
123

使い分けの指針

• 位置や長さが決まっている → SUBSTR() を使う（シンプル・高速）
• パターンで抽出したい → REGEXP_SUBSTR() を使う（柔軟）
• パフォーマンスが重要 → 可能な限り SUBSTR() を選択

-- NULLの場合の動作
SELECT SUBSTR(NULL, 1, 3) AS result FROM dual;
-- 結果: NULL

-- NVLで安全に処理
SELECT SUBSTR(NVL(emp_name, '（未設定）'), 1, 5) AS result
FROM employees;

-- 開始位置が文字列長を超える場合
SELECT SUBSTR('ABC', 10, 3) AS result FROM dual;
-- 結果: NULL

-- 文字数が残りの文字列長より大きい場合
SELECT SUBSTR('ABC', 2, 100) AS result FROM dual;
-- 結果: BC（エラーにならず、残りすべてが返る）

ポイント：文字数に大きな値を指定してもエラーにはなりません。残りの文字数分だけ返されるので、「開始位置以降すべて取得」する場合に大きな値を指定するテクニックも使えます。ただし、可読性を考えると文字数を省略するほうが望ましいです。

-- インデックスが効かない（フルスキャンになる可能性）
SELECT * FROM employees
WHERE SUBSTR(emp_code, 1, 3) = 'ABC';

-- 代替案1: LIKEを使う（インデックスが効く）
SELECT * FROM employees
WHERE emp_code LIKE 'ABC%';

-- 代替案2: 関数インデックスを作成
CREATE INDEX idx_emp_code_prefix
ON employees (SUBSTR(emp_code, 1, 3));

関数インデックスについて

• WHERE句で SUBSTR() を使う検索が頻繁にある場合は、関数インデックス（Function-Based Index）を検討しましょう
• ただし、インデックスの維持コスト（INSERT/UPDATE時のオーバーヘッド）も考慮してください
• 前方一致の検索であれば LIKE 'prefix%' のほうがシンプルで効率的です

-- 非効率: 同じカラムに対してSUBSTRを何度も呼ぶ
SELECT
  SUBSTR(data, 1, 3)   AS part1,
  SUBSTR(data, 4, 5)   AS part2,
  SUBSTR(data, 9, 2)   AS part3,
  SUBSTR(data, 11, 4)  AS part4,
  SUBSTR(data, 15)     AS part5
FROM large_table; -- 数百万件

-- ヒント: 多数のSUBSTR呼び出しは、
-- PL/SQL処理やアプリケーション側での分割も検討

-- 括弧を除いた中身を取得 「(内容)」→「内容」
SELECT SUBSTR('(Oracle)', 2, LENGTH('(Oracle)') - 2) AS result
FROM dual;

実行結果

RESULT
------
Oracle

-- 先頭文字を大文字に変換（INITCAP相当の手動実装）
SELECT
  UPPER(SUBSTR('oracle database', 1, 1)) ||
  SUBSTR('oracle database', 2) AS result
FROM dual;

実行結果

RESULT
---------------
Oracle database