【SQL】SUBSTR / SUBSTRING関数完全ガイド｜文字列の切り出し・LEFT・RIGHT・INSTR連携・実務パターンまで解説

2024.05.09

SQLで文字列の一部を取り出す操作は、メールアドレスからドメインを抽出する・コードの先頭桁を確認する・個人情報をマスキングするなど、実務で頻繁に発生します。そのための主要関数が SUBSTR / SUBSTRING です。

関数名・構文・負の開始位置の扱いがDBMSによって異なるため、移植性を意識した書き方が必要です。本記事では SUBSTR / SUBSTRING の全構文・LEFT / RIGHT との使い分け・INSTR / POSITION / CHARINDEX との組み合わせ・実務パターン集まで網羅します。

この記事で学べること：SUBSTR/SUBSTRINGの全構文・DBMS別の関数名と対応状況・開始位置の1始まりと負値・長さ省略時の動作・LEFT/RIGHTとの使い分け・INSTR/POSITION/CHARINDEXで位置を動的に取得して切り出す・メールアドレス分解・郵便番号分割・マスキング・固定幅フィールドパースなどの実務パターン集
スポンサーリンク

基本構文と引数

SUBSTR / SUBSTRING は3つの引数を取ります（3つ目は省略可能）。位置は1始まり（先頭文字が1）で、ほぼすべてのDBMSで共通です。

基本構文
-- 共通構文（SUBSTR / SUBSTRING どちらも同じ引数）
SUBSTR(文字列, 開始位置)
SUBSTR(文字列, 開始位置, 取り出す文字数)

SUBSTRING(文字列, 開始位置)
SUBSTRING(文字列, 開始位置, 取り出す文字数)

-- SQL標準構文（FROM / FOR キーワード形式）
SUBSTRING(文字列 FROM 開始位置)
SUBSTRING(文字列 FROM 開始位置 FOR 取り出す文字数)
引数 説明 省略時
文字列 切り出し元の文字列またはカラム名 —（必須）
開始位置 切り出し開始の文字位置。先頭は 1（0を指定すると1と同じ扱い※DB依存） —（必須）
取り出す文字数 何文字分取り出すか。省略すると末尾まですべて取り出す 末尾まで取り出す
基本の使い方
-- 1文字目から5文字取り出す
SELECT SUBSTR('Hello, World!', 1, 5);   -- 'Hello'
SELECT SUBSTRING('Hello, World!', 1, 5); -- 'Hello'

-- 8文字目から末尾まで
SELECT SUBSTR('Hello, World!', 8);      -- 'World!'

-- カラムに適用
SELECT
    customer_code,
    SUBSTR(customer_code, 1, 2) AS prefix,   -- 先頭2文字（地域コード等）
    SUBSTR(customer_code, 3)    AS suffix     -- 3文字目以降
FROM customers;

DBMS別の関数名と対応状況

DBMS SUBSTR SUBSTRING SUBSTRING FROM/FOR LEFT / RIGHT
MySQL ○（SUBSTRINGの別名） ○ ○ ○
PostgreSQL ○ ○ ○（SQL標準） ○
Oracle ○ ×（使えない） × ×（SUBSTR代用）
SQL Server ×（使えない） ○ × ○
移植性の注意：Oracle は SUBSTRING が使えず SUBSTR のみ対応。SQL Server は SUBSTR が使えず SUBSTRING のみ対応。全DBMS対応が必要な場合は SUBSTR（Oracle共通）を使い、SQL Serverは別途対応が必要です。
DBMS別の書き方
-- MySQL / PostgreSQL: SUBSTR と SUBSTRING は同じ
SELECT SUBSTR('Hello', 1, 3);       -- 'Hel'
SELECT SUBSTRING('Hello', 1, 3);    -- 'Hel'
SELECT SUBSTRING('Hello' FROM 1 FOR 3); -- 'Hel'（SQL標準形式）

-- Oracle: SUBSTR のみ（SUBSTRING は存在しない）
SELECT SUBSTR('Hello', 1, 3) FROM dual;  -- 'Hel'

-- SQL Server: SUBSTRING のみ（SUBSTR は存在しない）
SELECT SUBSTRING('Hello', 1, 3);    -- 'Hel'

開始位置の詳細：1始まり・0指定・負の値

1始まりと0を指定した場合

開始位置の挙動
-- 標準: 1が先頭文字
SELECT SUBSTR('ABCDE', 1, 3);  -- 'ABC'（先頭から3文字）
SELECT SUBSTR('ABCDE', 2, 3);  -- 'BCD'（2文字目から3文字）
SELECT SUBSTR('ABCDE', 3, 3);  -- 'CDE'（3文字目から3文字）

-- 0を指定した場合（DBMSによって動作が異なる）
SELECT SUBSTR('ABCDE', 0, 3);
-- MySQL:      'ABC'（0は1と同じ扱い）
-- Oracle:     'AB'（0を先頭の1つ前と解釈、取り出し文字数が1減る）
-- PostgreSQL: 'AB'（同様に1手前として扱う）
-- SQL Server: SUBSTRING('ABCDE', 0, 3) → 'AB'（同様）
開始位置に0を使わない：0の動作はDBMSによって異なります（MySQLは1と同等、Oracle/PostgreSQL/SQL Serverは1手前）。意図しない結果を避けるために、開始位置は常に1以上の整数を指定してください。

負の開始位置（Oracle独自機能）

Oracle: 負の開始位置で末尾から数える
-- Oracle のみ: 負の開始位置 → 末尾から数えて開始
SELECT SUBSTR('Hello, World!', -6)    FROM dual;  -- 'World!'（末尾6文字）
SELECT SUBSTR('Hello, World!', -6, 5) FROM dual;  -- 'World'（末尾6文字目から5文字）

-- 末尾3文字を取得（Oracle）
SELECT SUBSTR(product_code, -3) FROM products;

-- MySQL / PostgreSQL / SQL Server では負の開始位置は使えない
-- → 代替: SUBSTR(str, LENGTH(str) - N + 1, N) または RIGHT(str, N)

長さを省略した場合

長さ省略 = 末尾まで取り出す
-- 長さを省略すると指定位置から末尾まで取り出す（全DB共通）
SELECT SUBSTR('Hello, World!', 8);        -- 'World!'
SELECT SUBSTRING('Hello, World!', 8);     -- 'World!'
SELECT SUBSTRING('Hello, World!' FROM 8); -- 'World!'（SQL標準）

-- カラムの先頭3文字を除いた残り全部
SELECT SUBSTR(description, 4) FROM articles;  -- 4文字目以降

LEFT / RIGHT：先頭・末尾N文字を取り出す短縮形

先頭または末尾から固定文字数を取り出す場合は LEFT / RIGHT がシンプルです。MySQL・PostgreSQL・SQL Server で使えますが、Oracle には存在しません。

LEFT / RIGHT の使い方
-- LEFT: 先頭からN文字
SELECT LEFT('Hello, World!', 5);    -- 'Hello'  （MySQL / PostgreSQL / SQL Server）
SELECT LEFT(product_code, 2);       -- 先頭2文字（カテゴリコード等）

-- RIGHT: 末尾からN文字
SELECT RIGHT('Hello, World!', 6);   -- 'World!'
SELECT RIGHT(phone, 4);             -- 電話番号の下4桁

-- Oracle での代替（LEFT / RIGHT がないため SUBSTR で代用）
SELECT SUBSTR('Hello, World!', 1, 5)            FROM dual;  -- LEFT相当
SELECT SUBSTR('Hello, World!', -6)              FROM dual;  -- RIGHT相当（負位置）
SELECT SUBSTR('Hello, World!', LENGTH('Hello, World!') - 5) FROM dual;  -- RIGHT相当（全DB移植可）
操作 MySQL / PostgreSQL / SQL Server Oracle
先頭N文字 LEFT(str, N) SUBSTR(str, 1, N)
末尾N文字 RIGHT(str, N) SUBSTR(str, -N) または SUBSTR(str, LENGTH(str)-N+1)
M文字目からN文字 SUBSTR(str, M, N) SUBSTR(str, M, N)

位置検索関数と組み合わせて動的に切り出す

「@より前のユーザー名を取り出す」「最後のスラッシュ以降のファイル名だけ取り出す」など、区切り文字の位置を動的に取得して切り出すパターンでは、SUBSTR と位置検索関数を組み合わせます。

DBMS 文字列内の位置を返す関数 見つからない場合の戻り値
MySQL INSTR(str, search) / LOCATE(search, str) / POSITION(search IN str) 0
PostgreSQL STRPOS(str, search) / POSITION(search IN str) 0
Oracle INSTR(str, search [, start [, n]]) 0
SQL Server CHARINDEX(search, str [, start]) 0
メールアドレスからユーザー名とドメインを抽出
-- MySQL / Oracle
SELECT
    email,
    SUBSTR(email, 1, INSTR(email, '@') - 1) AS username,  -- @より前
    SUBSTR(email, INSTR(email, '@') + 1)    AS domain      -- @より後
FROM users;
-- 'yamada@example.com' → username='yamada', domain='example.com'

-- PostgreSQL
SELECT
    email,
    SUBSTR(email, 1, STRPOS(email, '@') - 1) AS username,
    SUBSTR(email, STRPOS(email, '@') + 1)    AS domain
FROM users;

-- SQL Server
SELECT
    email,
    SUBSTRING(email, 1, CHARINDEX('@', email) - 1) AS username,
    SUBSTRING(email, CHARINDEX('@', email) + 1, LEN(email)) AS domain
FROM users;
URLからファイル名を抽出（最後のスラッシュ以降）
-- Oracle: INSTR で最後の / の位置を取得
SELECT
    url,
    SUBSTR(url, INSTR(url, '/', -1) + 1) AS filename
FROM pages;
-- 'https://example.com/images/photo.jpg' → 'photo.jpg'
-- INSTR(str, '/', -1) は末尾から検索して最初に見つかった位置を返す

-- MySQL: 最後の / の位置を取得（末尾検索は LENGTH + LOCATE の組み合わせ）
SELECT
    url,
    SUBSTR(url, LENGTH(url) - LOCATE('/', REVERSE(url)) + 2) AS filename
FROM pages;

-- PostgreSQL
SELECT
    url,
    SUBSTR(url, LENGTH(url) - STRPOS(REVERSE(url), '/') + 2) AS filename
FROM pages;
区切り文字で分割（カンマ・アンダースコアなど）
-- アンダースコアで「カテゴリ_連番」形式のコードを分割
-- 例: 'SQL_001' → カテゴリ='SQL', 連番='001'

-- MySQL / Oracle
SELECT
    code,
    SUBSTR(code, 1, INSTR(code, '_') - 1) AS category,
    SUBSTR(code, INSTR(code, '_') + 1)    AS seq_no
FROM products;

-- PostgreSQL（SPLIT_PART が使えるならより簡潔）
SELECT
    code,
    SPLIT_PART(code, '_', 1) AS category,  -- PostgreSQL独自
    SPLIT_PART(code, '_', 2) AS seq_no
FROM products;
PostgreSQL独自の SPLIT_PART：SPLIT_PART(文字列, 区切り文字, N) は区切り文字で分割したN番目の要素を返します。SUBSTR + STRPOSのネストより格段にシンプルで、CSV形式や _ 区切りのコード分解に便利です。

実務でよく使うパターン集

郵便番号の分割（ハイフン前後）

郵便番号の分割
-- '123-4567' → '123' と '4567' に分割
SELECT
    zip_code,
    SUBSTR(zip_code, 1, 3) AS zip_prefix,  -- ハイフン前（固定3桁）
    SUBSTR(zip_code, 5, 4) AS zip_suffix   -- ハイフン後（固定4桁）
FROM addresses;

-- ハイフンなし形式に変換
SELECT
    SUBSTR(zip_code, 1, 3) || '-' || SUBSTR(zip_code, 4)  -- ハイフンなしに付与
FROM addresses
WHERE zip_code NOT LIKE '%-%';

電話番号のマスキング（下4桁を****に）

個人情報マスキング
-- 電話番号の下4桁を **** に（全DB共通）
SELECT
    phone,
    SUBSTR(phone, 1, LENGTH(phone) - 4) || '****' AS masked_phone
FROM customers;
-- '09012345678' → '0901234****'

-- 上4桁だけ残して残りをマスク
SELECT
    name,
    LEFT(name, 1) || REPEAT('*', LENGTH(name) - 1) AS masked_name  -- MySQL/PostgreSQL
FROM customers;
-- '田中太郎' → '田***'

-- SQL Server
SELECT
    name,
    SUBSTRING(name, 1, 1) + REPLICATE('*', LEN(name) - 1) AS masked_name
FROM customers;

日付文字列から年・月・日を抽出

文字列形式の日付を分解（’YYYYMMDD’形式）
-- '20240315' → year='2024', month='03', day='15'
SELECT
    date_str,
    SUBSTR(date_str, 1, 4) AS year_part,
    SUBSTR(date_str, 5, 2) AS month_part,
    SUBSTR(date_str, 7, 2) AS day_part,
    -- ハイフン区切りに変換
    SUBSTR(date_str, 1, 4) || '-' || SUBSTR(date_str, 5, 2) || '-' || SUBSTR(date_str, 7, 2)
        AS formatted_date
FROM date_strings;

-- 'YYYY/MM/DD' 形式から月だけ取り出す
SELECT SUBSTR(date_str, 6, 2) AS month_part  -- 6文字目から2文字（MM部分）
FROM date_strings;

固定幅フィールドのパース

レガシーシステムからのデータや、バッチ連携ファイルには固定幅で複数フィールドを詰めた形式が存在します。SUBSTRで各フィールドを切り出せます。

固定幅フィールドのパース
-- 固定幅データ例（30文字）:
-- 位置  1- 4: 年度（4文字）
-- 位置  5- 6: 月（2文字）
-- 位置  7-16: 顧客コード（10文字、右詰めスペース）
-- 位置 17-30: 金額（14文字、右詰めゼロ埋め）

SELECT
    raw_line,
    TRIM(SUBSTR(raw_line,  1,  4))  AS fiscal_year,
    TRIM(SUBSTR(raw_line,  5,  2))  AS month,
    TRIM(SUBSTR(raw_line,  7, 10))  AS customer_code,
    CAST(TRIM(SUBSTR(raw_line, 17, 14)) AS DECIMAL(14,2)) AS amount
FROM fixed_width_import;

コードの先頭でテーブルを振り分ける（CASE WHEN + SUBSTR）

コード先頭でカテゴリ判定
-- コードの先頭2文字でカテゴリを判定してラベル付け
SELECT
    product_code,
    CASE SUBSTR(product_code, 1, 2)
        WHEN 'PC' THEN 'パソコン'
        WHEN 'MO' THEN 'モバイル'
        WHEN 'AC' THEN 'アクセサリ'
        ELSE            'その他'
    END AS category_name
FROM products;

-- 先頭1文字で国コード判定（MySQL）
SELECT
    order_no,
    LEFT(order_no, 1)                    AS country_code,
    SUBSTR(order_no, 2)                  AS local_order_no
FROM orders;

SUBSTR + UPDATE でデータを修正する

SUBSTR + UPDATE で先頭/末尾の余分な文字を削除
-- 先頭に誤って追加された接頭辞「XX」を除去
-- 'XXABC001' → 'ABC001'
UPDATE products
SET    product_code = SUBSTR(product_code, 3)
WHERE  product_code LIKE 'XX%';

-- 確認（UPDATE前に SELECT で確認）
SELECT product_code, SUBSTR(product_code, 3) AS new_code
FROM   products
WHERE  product_code LIKE 'XX%';

REGEXP_SUBSTRとの使い分け（PostgreSQL・Oracle・MySQL 8.0+）

抽出パターンが固定位置・固定文字数なら SUBSTR、正規表現でマッチした部分を取り出したい場合は REGEXP_SUBSTR を使います。

REGEXP_SUBSTR が必要なケース
-- 文字列から数字部分だけを抽出（位置が不定）
-- Oracle
SELECT REGEXP_SUBSTR('ABC123DEF456', '[0-9]+') FROM dual;   -- '123'（最初の数字列）

-- PostgreSQL（regexp_match）
SELECT (REGEXP_MATCH('ABC123DEF456', '[0-9]+'))[1];         -- '123'

-- MySQL 8.0+
SELECT REGEXP_SUBSTR('ABC123DEF456', '[0-9]+');              -- '123'

-- メールのドメイン部分を正規表現で抽出
SELECT REGEXP_SUBSTR(email, '@(.+)', 1, 1, 'e', 1)          -- Oracle
FROM users;

よくある質問

QSUBSTRとSUBSTRINGの違いは何ですか？
A機能は同じです。DBMSの違いで使用できる名前が異なります。MySQL・PostgreSQLはどちらも使えます。OracleはSUBSTRのみ（SUBSTRINGは存在しない）、SQL ServerはSUBSTRINGのみ（SUBSTRは存在しない）です。移植性を考えるなら、SQL Server以外はSUBSTRに統一し、SQL Server専用クエリではSUBSTRINGを使います。
Q開始位置を超えた位置を指定するとどうなりますか？
A開始位置が文字列長より大きい場合は空文字 '' が返ります（エラーにはなりません）。例: SUBSTR('Hello', 10) → ''。ただしSQL ServerのSUBSTRINGは開始位置が1未満でも動作し（1とみなす）、長さが負だとエラーになります。
Q末尾から数えてN文字取り出すには？
AOracleはSUBSTR(str, -N)（負の開始位置）が使えます。MySQL・PostgreSQL・SQL ServerはRIGHT(str, N)またはSUBSTR(str, LENGTH(str) - N + 1, N)を使います。全DB共通の書き方はSUBSTR(str, LENGTH(str) - N + 1)です（Oracleは-N推奨）。
Qマルチバイト文字（日本語）でSUBSTRを使う場合の注意点は？
AMySQLのSUBSTRはデフォルトで文字数単位で動作します（UTF-8でも1文字=1としてカウント）。OracleはNLS_CHARACTERSETによって動作が変わる場合があります。バイト単位で切り出したい場合はSUBSTRB（Oracle）やSUBSTRING（バイトモード）を明示的に使ってください。通常の文字列操作では文字数単位のSUBSTRで問題ありません。
QSUBSTRの結果が空文字になる場合のNULL変換は？
ANULLIF(SUBSTR(str, pos, len), '')を使うと、結果が空文字のときにNULLに変換できます。またCASE WHEN SUBSTR(str, pos, len) = '' THEN NULL ELSE SUBSTR(str, pos, len) ENDでも同様の結果が得られます。

まとめ

SUBSTR / SUBSTRING は文字列操作の基本中の基本ですが、DBMS間の違いを把握しておくことが重要です。

  • 関数名: Oracle は SUBSTR のみ・SQL Server は SUBSTRING のみ・MySQL/PostgreSQL は両方対応
  • 開始位置は1始まり: 0の指定はDBMSによって動作が異なるため使わない
  • 長さ省略 = 末尾まで: 第3引数を省略すると指定位置から末尾まで取り出す
  • 負の開始位置は Oracle のみ: 末尾からN文字は MySQL/PostgreSQL/SQL Server では RIGHT() を使う
  • 動的な位置切り出し: INSTR / STRPOS / CHARINDEX と組み合わせて区切り文字での分割に対応
  • 実務応用: メール分解・郵便番号分割・マスキング・固定幅パース・コード先頭での振り分け
関数の選び方：先頭N文字 → LEFT(str, N)（Oracle以外）または SUBSTR(str, 1, N)（全DB）、末尾N文字 → RIGHT(str, N)（Oracle以外）または SUBSTR(str, -N)（Oracle）、中間部分 → SUBSTR(str, 開始, 文字数)、区切り文字での分割 → SUBSTR + INSTR/STRPOS/CHARINDEX の組み合わせ

関連記事：LENGTH関数で文字列の長さを取得する方法、REPLACE関数完全ガイド、文字列結合完全ガイド（CONCAT・GROUP_CONCAT）、TRIM関数完全ガイド