はい、承知いたしました。MySQLのSUBSTR関数に関する詳細な解説記事を約5000語で記述します。記事の内容を直接表示します。

---

MySQLで文字列を切り取るSUBSTR関数の使い方【具体例付き】

1. はじめに：なぜ文字列操作が重要なのか

データベース、特にリレーショナルデータベースであるMySQLでは、構造化されたデータを扱いますが、実際の業務やアプリケーション開発においては、様々な形式の文字列データを扱う機会が頻繁に発生します。ユーザーからの入力データ、外部システムからの連携データ、ログ情報、設定値など、これらはしばしば単なる文字列としてデータベースに格納されます。

しかし、これらの文字列データは、そのままの形で利用できるとは限りません。例えば：

• メールアドレスからドメイン部分だけを取り出したい
• 商品コードの一部（例: ABC-12345-X から 12345 の部分）だけを使って集計したい
• 住所データから市区町村名だけを抽出したい
• 特定のフォーマット（例: YYYYMMDDhhmmss）で記録された日時文字列から、日付部分だけを取り出したい
• ログメッセージから、エラーコードや特定のキーワードの後ろにある情報を抜き出したい

このような場合、単にデータをSELECTして表示するだけでなく、文字列の一部を抽出したり、加工したりする必要があります。MySQLは、このような文字列操作のために豊富な組み込み関数を提供しており、その中でも最も基本的で頻繁に使用される関数の一つが SUBSTR 関数です。

SUBSTR 関数を使うことで、文字列の任意の位置から、指定した長さだけ、または末尾まで、部分文字列を効率的に抽出することができます。これは、データのクレンジング、レポート作成、条件抽出、他のシステムとの連携など、様々な場面で不可欠な機能です。

この記事では、MySQLの SUBSTR 関数（およびそのシノニムである SUBSTRING 関数）に焦点を当て、その基本的な使い方から、様々な応用例、使用上の注意点、そして関連する他の文字列関数との組み合わせまで、網羅的かつ詳細に解説します。豊富な具体例を通じて、SUBSTR 関数をマスターし、MySQLでの文字列操作の幅を広げることを目指します。

2. SUBSTR関数とは：基本的な構文と役割

SUBSTR 関数は、MySQLにおいて文字列から部分文字列を抽出するための主要な関数です。指定した文字列の中から、開始位置を指定し、必要であれば長さを指定して、その部分を取り出します。

2.1. 基本的な構文

SUBSTR 関数には、主に2つの基本的な構文があります。

1. 開始位置から最後までを抽出する場合:
   sql
SUBSTR(str, pos)
   または
   sql
SUBSTR(str FROM pos)

2. 開始位置から指定した長さだけを抽出する場合:
   sql
SUBSTR(str, pos, len)
   または
   sql
SUBSTR(str FROM pos FOR len)

それぞれの引数について説明します。

• str: これは、部分文字列を抽出したい元の文字列です。文字列リテラル、テーブルのカラム名、他の関数や式の結果など、評価結果が文字列となるものであれば指定できます。
• pos: これは、抽出を開始する位置を指定する数値です。MySQLの文字列インデックスは 1から始まります。つまり、文字列の最初の文字は位置1、2番目の文字は位置2、といった具合です。
  • pos が正の数の場合、文字列の先頭から数えてその位置が開始点となります。
  • pos が負の数の場合、文字列の末尾から数えてその位置が開始点となります。例えば、-1 は最後の文字、-2 は最後から2番目の文字、というように扱われます。
• len: これは、抽出する部分文字列の長さを指定する数値です。省略された場合（構文1）、指定した開始位置から文字列の最後までが抽出されます。
  • len が正の数の場合、開始位置からその長さだけ文字を抽出します。
  • len が 0 の場合、空の文字列 ('') が返されます。
  • len が負の数の場合、MySQL 8.0以降では LENGTH < 0 という警告とともに空の文字列 ('') が返されます（バージョンによってはエラーになることもあります）。負の長さを指定することは一般的ではありません。

2.2. SUBSTRとSUBSTRING：どちらを使うべきか？

MySQLでは、SUBSTR と SUBSTRING は完全に同じ関数です。どちらを使用しても、全く同じ結果が得られます。

sql
-- 以下の2つのクエリは同じ結果を返します
SELECT SUBSTR('Hello World', 7, 5);
SELECT SUBSTRING('Hello World', 7, 5);

歴史的な背景や、他のデータベースシステムとの互換性（SQL標準ではSUBSTRINGが一般的）から、どちらの名称もサポートされています。一般的には、SQL標準に準拠している SUBSTRING の方がよりポータブルであると言えますが、MySQLのコンテキストにおいては SUBSTR も全く問題なく使用できます。この記事では、説明を統一するために主に SUBSTR という名称を使用しますが、SUBSTRING に置き換えても動作することは覚えておいてください。また、FROM句やFOR句を使った形式も同様に利用可能です。

sql
-- これらも同じ結果を返します
SELECT SUBSTR('Hello World' FROM 7 FOR 5);
SELECT SUBSTRING('Hello World' FROM 7 FOR 5);

3. SUBSTR関数の基本的な使い方：様々なシナリオ

ここでは、SUBSTR 関数の基本的な使い方を、具体的なSQL例を交えて見ていきます。様々な引数の組み合わせによる挙動を理解することが重要です。

例に使用する文字列は、シンプルな英数字と、マルチバイト文字である日本語を含んだものを両方使用します。MySQLの文字コード設定（特にUTF-8などのマルチバイト文字を正しく扱える設定）が前提となります。

3.1. SUBSTR(str, pos) – 開始位置から最後までを取得

この構文は、指定した開始位置 pos から、元の文字列 str の末尾までを抽出します。

3.1.1. pos が正の場合（先頭から数える）

インデックスは1から始まります。

例1：基本的な抽出

sql
SELECT SUBSTR('abcdefg', 3);
-- 結果: 'cdefg'
-- 説明: 3番目の文字 'c' から末尾までを抽出します。

例2：先頭から開始

sql
SELECT SUBSTR('abcdefg', 1);
-- 結果: 'abcdefg'
-- 説明: 1番目の文字から末尾まで、つまり文字列全体を抽出します。

例3：特定の文字から開始

sql
SELECT SUBSTR('Tokyo_Japan', 7);
-- 結果: 'Japan'
-- 説明: 7番目の文字 '_' の次の 'J' から末尾までを抽出します。（'T'=1, 'o'=2, 'k'=3, 'y'=4, 'o'=5, '_'=6, 'J'=7）

例4：日本語文字列からの抽出

sql
SELECT SUBSTR('こんにちは世界', 4);
-- 結果: '世界'
-- 説明: 日本語でも文字単位で数えます。「こ」(1), 「ん」(2), 「に」(3), 「ち」(4)。4番目の「ち」から末尾まで、つまり「ちわ世界」を抽出します。
-- ※注意: 古いMySQLや設定によってはバイト単位で処理される可能性がありますが、現在の標準的な設定（UTF-8など）では文字単位で処理されます。

例5：日本語文字列の特定の文字から開始

sql
SELECT SUBSTR('東京都千代田区', 4);
-- 結果: '千代田区'
-- 説明: 「東」(1), 「京」(2), 「都」(3), 「千」(4)。4番目の「千」から末尾までを抽出します。

3.1.2. pos が負の場合（末尾から数える）

pos が負の場合、文字列の末尾から数えて開始位置を特定します。-1 は最後の文字、-2 は最後から2番目の文字、というようになります。抽出は、特定された位置から 末尾に向かって ではなく、特定された位置から 文字列の最後まで 行われます。

例6：末尾の文字から抽出

sql
SELECT SUBSTR('abcdefg', -1);
-- 結果: 'g'
-- 説明: 末尾から1番目の文字 'g' から末尾までを抽出します。

例7：末尾から数文字を抽出

sql
SELECT SUBSTR('abcdefg', -3);
-- 結果: 'efg'
-- 説明: 末尾から3番目の文字 'e' から末尾までを抽出します。（'g'=-1, 'f'=-2, 'e'=-3）

例8：日本語文字列の末尾から抽出

sql
SELECT SUBSTR('こんにちは世界', -2);
-- 結果: '世界'
-- 説明: 末尾から2番目の文字「世」から末尾までを抽出します。（「界」=-1, 「世」=-2）

例9：日本語文字列の末尾から数文字を抽出

sql
SELECT SUBSTR('東京都千代田区', -3);
-- 結果: '千代田区'
-- 説明: 末尾から3番目の文字「千」から末尾までを抽出します。（「区」=-1, 「田」=-2, 「代」=-3, 「千」=-4, 「田」=-5...あれ？「千」は5番目か。末尾から数えるときは注意が必要。「区」=-1, 「田」=-2, 「代」=-3, 「千」=-4, 「都」=-5, 「京」=-6, 「東」=-7。末尾から3番目は「代」。）
-- 正しい結果は: '代田区'
SELECT SUBSTR('東京都千代田区', -3);
-- 結果: '代田区'
自己修正: 例9の説明が間違っていました。負のインデックスは末尾からの位置を示し、抽出はそこから末尾までです。

3.1.3. pos が文字列長を超える場合

pos が文字列の全長よりも大きい正の数、あるいは文字列の全長よりも絶対値が大きい負の数の場合、開始位置が文字列の範囲外になります。この場合、空の文字列 ('') が返されます。

例10：pos が文字列長を超える

“`sql
SELECT SUBSTR(‘abc’, 4);
— 結果: ”
— 説明: 文字列長は3。4番目の位置は存在しないため、空の文字列が返されます。

SELECT SUBSTR(‘abc’, -4);
— 結果: ”
— 説明: 文字列長は3。末尾から4番目の位置 (-4) は、先頭の文字 (-3) よりもさらに左にあるため、範囲外となり空の文字列が返されます。
“`

3.2. SUBSTR(str, pos, len) – 開始位置から指定長だけを取得

この構文は、指定した開始位置 pos から、指定した長さ len だけ部分文字列を抽出します。これが SUBSTR 関数の最も一般的な使い方かもしれません。

3.2.1. pos が正の場合（先頭から数える）

例11：基本的な抽出（先頭から指定長）

sql
SELECT SUBSTR('abcdefg', 1, 3);
-- 結果: 'abc'
-- 説明: 1番目の文字 'a' から3文字を抽出します。

例12：途中の位置から指定長

sql
SELECT SUBSTR('abcdefg', 3, 4);
-- 結果: 'cdef'
-- 説明: 3番目の文字 'c' から4文字を抽出します。

例13：日本語文字列から指定長

sql
SELECT SUBSTR('こんにちは世界', 1, 5);
-- 結果: 'こんにちは'
-- 説明: 1番目の文字「こ」から5文字を抽出します。

例14：日本語文字列の途中から指定長

sql
SELECT SUBSTR('東京都千代田区', 4, 3);
-- 結果: '千代田'
-- 説明: 4番目の文字「千」から3文字を抽出します。

3.2.2. pos が負の場合（末尾から数える）

pos が負の場合、開始位置は末尾から数えられます。抽出は、その開始位置から len 文字だけ行われます。

例15：末尾から数えて、指定長を抽出

sql
SELECT SUBSTR('abcdefg', -3, 2);
-- 結果: 'ef'
-- 説明: 末尾から3番目の文字 'e' を開始位置とし、そこから2文字 ('e', 'f') を抽出します。

例16：日本語文字列の末尾から数えて、指定長を抽出

sql
SELECT SUBSTR('東京都千代田区', -4, 2);
-- 結果: '千代'
-- 説明: 末尾から4番目の文字「千」を開始位置とし、そこから2文字（「千」、「代」）を抽出します。（「区」=-1, 「田」=-2, 「代」=-3, 「千」=-4）

3.2.3. len が残りの文字列長を超える場合

指定した長さ len が、開始位置 pos から文字列の末尾までの残りの長さよりも大きい場合、抽出は文字列の末尾で終了します。エラーにはなりません。

例17：len が残りの長さより大きい

sql
SELECT SUBSTR('abcdefg', 5, 10);
-- 結果: 'efg'
-- 説明: 5番目の文字 'e' から開始し、10文字を抽出しようとしますが、残りは'efg'の3文字しかないので、'efg'が返されます。

例18：日本語文字列で len が残りの長さより大きい

sql
SELECT SUBSTR('こんにちは世界', 7, 10);
-- 結果: '世界'
-- 説明: 7番目の文字「世」から開始し、10文字を抽出しようとしますが、残りは「世界」の2文字しかないので、「世界」が返されます。（「こ」=1, ..., 「ち」=4, 「わ」=5, 「世」=6, 「界」=7）
-- ※例4の説明誤りから修正。「こんにちは世界」は7文字。
-- SELECT SUBSTR('こんにちは世界', 6, 10); の場合
SELECT SUBSTR('こんにちは世界', 6, 10);
-- 結果: '世界'
-- 説明: 6番目の文字「世」から開始し、10文字を抽出しますが、残りは「世界」の2文字なので、「世界」が返されます。
自己修正: 例18の説明も誤り。「こんにちは世界」は7文字。6番目は「世」。開始位置6から残りは「世界」の2文字。

3.2.4. len が0の場合

len が 0 の場合、開始位置に関わらず、常に空の文字列 ('') が返されます。

例19：len が0

“`sql
SELECT SUBSTR(‘abcdefg’, 3, 0);
— 結果: ”
— 説明: 長さ0を指定したため、空の文字列が返されます。

SELECT SUBSTR(‘abcdefg’, -3, 0);
— 結果: ”
— 説明: 長さ0を指定したため、空の文字列が返されます。
“`

3.2.5. len が負の場合

len が負の場合、MySQL 8.0以降では警告が出力され、空の文字列 ('') が返されます。それ以前のバージョンではエラーになることもあります。負の長さを指定することは、意図しない挙動を引き起こす可能性があるため避けるべきです。

例20：len が負

sql
-- MySQL 8.0以降では警告とともに '' を返す
SELECT SUBSTR('abcdefg', 3, -1);
-- 結果: '' (警告あり)
-- 説明: 負の長さを指定したため、空の文字列が返されます。

このように、SUBSTR 関数は pos と len の組み合わせによって、様々な部分文字列抽出ニーズに対応できます。特に pos の負の値の扱いと、len が残りの文字列長を超える場合の挙動を理解しておくことが重要です。

4. SUBSTR関数の応用例

SUBSTR 関数は、単独で使用するだけでなく、他の関数と組み合わせることでより複雑で実用的な文字列操作を実現できます。ここでは、いくつかの典型的な応用例を紹介します。

4.1. メールアドレスのドメイン部分を抽出する

メールアドレスは通常、ユーザー名@ドメイン名 の形式をしています。@ の後ろの部分（ドメイン名）を抽出したい場合があります。この場合、LOCATE 関数を使って @ の位置を見つけ、その位置を SUBSTR の開始位置として利用します。

LOCATE(substring, string) は、string の中に substring が最初に現れる位置を返します。見つからない場合は 0 を返します。

sql
-- メールアドレス '@' の位置を見つけて、その1つ後ろから最後までを抽出
SELECT
email,
SUBSTR(email, LOCATE('@', email) + 1) AS domain
FROM
users
WHERE
email IS NOT NULL;

解説:
1. LOCATE('@', email) で、email カラムの値に含まれる @ の位置を取得します。
2. その位置に 1 を加えることで、@ の直後の文字の位置を開始点とします。
3. SUBSTR(email, 開始位置) の形式で、その開始位置からメールアドレスの末尾までを抽出します。
4. AS domain で抽出結果に domain という別名を付けています。

もし @ が含まれていないメールアドレス（例えば、不正なデータ）がある場合、LOCATE('@', email) は 0 を返します。SUBSTR(email, 0 + 1) となり、結果としてメールアドレス全体が返されます。必要に応じて、LOCATE の結果が 0 より大きい場合にのみ抽出を行う、といった条件を加えることもできます。

sql
SELECT
email,
CASE
WHEN LOCATE('@', email) > 0 THEN SUBSTR(email, LOCATE('@', email) + 1)
ELSE NULL -- または email 全体、あるいはエラーを示す値
END AS domain_or_null
FROM
users;

4.2. ファイルパスからファイル名を抽出する

ファイルパス（例: /usr/local/bin/mysql や C:\Users\user\document.txt）から、ディレクトリ部分を除いたファイル名だけを抽出したい場合があります。この場合、パスの区切り文字（/ または \）が最後に出現する位置を探し、その位置の次の文字から最後までを抽出します。LOCATE は最初に出現する位置を返しますが、最後に出現する位置は SUBSTRING_INDEX を使う方が簡単です。ただし、SUBSTR と他の関数を組み合わせる例として、LOCATE を応用することも可能です（少し複雑になりますが）。より直感的かつ効率的なのは SUBSTRING_INDEX と SUBSTR の組み合わせです。

SUBSTRING_INDEX(str, delim, count) は、文字列 str を区切り文字 delim で分割し、count 番目の区切り文字までの部分を返します。count が負の場合、末尾から数えます。

“`sql
— ファイルパスの例
SELECT SUBSTRING_INDEX(‘/usr/local/bin/mysql’, ‘/’, -1);
— 結果: ‘mysql’

SELECT SUBSTRING_INDEX(‘C:\Users\user\document.txt’, ‘\’, -1);
— 結果: ‘document.txt’

— SUBSTR を使って同じことを実現するには…少しトリッキー
— 最後の ‘/’ または ‘\’ の位置をどう見つけるか？
— 長さから、最後の ‘/’ の位置を引いて、そこから切り出す、という方法
SELECT
filepath,
SUBSTR(filepath, LENGTH(filepath) – LOCATE(‘/’, REVERSE(filepath)) + 2) AS filename_pos
FROM
file_data
WHERE filepath LIKE ‘%/%’; — 例として / 区切りを含むパスのみ

— LOCATE と REVERSE を組み合わせる方法
— REVERSE(‘/usr/local/bin/mysql’) -> ‘lqs ym/nib/lacol/rsu/’
— LOCATE(‘/’, REVERSE(‘/usr/local/bin/mysql’)) -> 5 (末尾から5番目に ‘/’ がある)
— LENGTH(‘/usr/local/bin/mysql’) -> 20
— 開始位置 = 全長 – (末尾からの位置) + 2
— = 20 – 5 + 2 = 17
— SUBSTR(‘/usr/local/bin/mysql’, 17) -> ‘mysql’

— 別の方法：SUBSTRING_INDEX で末尾の要素を取得するのがシンプル
SELECT
filepath,
SUBSTRING_INDEX(filepath, ‘/’, -1) AS filename_simple
FROM
file_data;

— Windowsパス (‘\’) の場合は \ をエスケープする必要がある
SELECT
filepath,
SUBSTRING_INDEX(filepath, ‘\’, -1) AS filename_windows
FROM
file_data;

— 両方に対応するには CASE や IF を使うか、REPLACE で統一するなど工夫が必要
SELECT
filepath,
SUBSTRING_INDEX(REPLACE(filepath, ‘\’, ‘/’), ‘/’, -1) AS filename_cross_platform
FROM
file_data;
``
**解説:**SUBSTR単独では最後の区切り文字を見つけるのが難しいため、このようなケースではSUBSTRING_INDEX(str, delim, -1)を使うのが最も効率的で推奨される方法です。これはSUBSTR` と直接関係ありませんが、ファイル名抽出という一般的なタスクにおいて、どの関数が適しているかを示す例として含めました。

しかし、もし「最後のスラッシュの次の文字から末尾まで」を SUBSTR で どうしても 実現したい場合は、REVERSE 関数と LOCATE 関数を組み合わせて、文字列を反転させてから最初の区切り文字の位置を探し、その情報を元の文字列の長さと組み合わせて SUBSTR の開始位置を計算するという方法があります。上記 filename_pos の例がそれです。LENGTH(filepath) は文字列の全長、LOCATE('/', REVERSE(filepath)) は反転した文字列における ‘/’ の位置（つまり元の文字列の末尾からの位置）、それに2を足すのは、末尾からの位置が1-basedであることと、その位置の 次 から抽出を開始するためです。この方法は複雑になりがちなので、現実的には SUBSTRING_INDEX を推奨します。

4.3. YYYY-MM-DD形式の日付文字列から年、月、日を抽出する

'YYYY-MM-DD' のような固定フォーマットの日付文字列から、年、月、日をそれぞれ抽出したい場合があります。SUBSTR 関数は、固定長または固定位置のデータ抽出に非常に適しています。

sql
SELECT
date_string,
SUBSTR(date_string, 1, 4) AS year, -- 1文字目から4文字（YYYY）
SUBSTR(date_string, 6, 2) AS month, -- 6文字目から2文字（MM）
SUBSTR(date_string, 9, 2) AS day -- 9文字目から2文字（DD）
FROM
sales
WHERE
date_string LIKE '____-__-__'; -- フォーマットが正しいことを確認（任意）

解説:
* YYYY-MM-DD フォーマットでは、年は1文字目から4文字、月は6文字目から2文字、日は9文字目から2文字というように、各要素の位置と長さが固定されています。
* SUBSTR(date_string, 1, 4) で年、SUBSTR(date_string, 6, 2) で月、SUBSTR(date_string, 9, 2) で日を正確に抽出できます。
* WHERE date_string LIKE '____-__-__' は、データが期待するフォーマットであることを簡易的に確認するためのものです。より厳密なチェックが必要な場合は、他の文字列関数や正規表現を使うこともあります。

この方法は、フォーマットが厳密に固定されている場合に有効です。もし区切り文字が変わる可能性がある、あるいは要素の桁数が変動する可能性がある場合は、SUBSTRING_INDEX や正規表現関数（REGEXP_SUBSTR など）の方が適しているかもしれません。

4.4. 特定のプレフィックスを持つ文字列を抽出する

特定のプレフィックス（接頭辞）が付いている文字列から、そのプレフィックス以降の部分を抽出したい場合があります。例えば、商品コードが ITEM-12345 のような形式で、ITEM- というプレフィックスを除去して 12345 だけを取得したい場合などです。

プレフィックスの長さを知っていれば、その長さ+1を開始位置として SUBSTR できます。

sql
-- プレフィックス 'ITEM-' (長さ5) を除去する
SELECT
product_code,
SUBSTR(product_code, 6) AS item_number -- 6文字目から最後まで
FROM
products
WHERE
product_code LIKE 'ITEM-%'; -- プレフィックスが付いているデータを対象にする

解説:
* プレフィックス ITEM- の長さは5文字です。その次の文字（6文字目）から最後までを抽出することで、プレフィックスを除去した部分を取得できます。
* WHERE product_code LIKE 'ITEM-%' は、処理対象を限定するための条件です。LIKE 演算子を使うことで、簡単にプレフィックスを持つデータを選択できます。

この方法は、プレフィックスが固定長である場合に有効です。プレフィックスが可変長である場合、例えば A-123, BB-456, CCC-789 のように - より前の部分の長さが異なる場合は、LOCATE 関数を使って - の位置を見つけ、その位置 + 1 を開始位置とする必要があります。

sql
-- 区切り文字 '-' の後ろの部分を抽出する
SELECT
code,
SUBSTR(code, LOCATE('-', code) + 1) AS value
FROM
codes
WHERE
LOCATE('-', code) > 0; -- '-' を含むデータを対象にする

解説:
* LOCATE('-', code) で、文字列 code に含まれる - の最初の位置を取得します。
* その位置に 1 を加えることで、- の直後の文字の位置を開始点とします。
* SUBSTR(code, 開始位置) の形式で、- の直後から文字列の末尾までを抽出します。
* WHERE LOCATE('-', code) > 0 は、- が含まれている行のみを処理するための条件です。

4.5. 固定長フィールドからのデータ抽出（古いデータ形式など）

一部の古いシステムやデータ形式では、データが固定長フィールドとして格納されていることがあります。例えば、1行の文字列の中に、あるカラムの値が1桁目から10桁目、別のカラムの値が11桁目から15桁目、といったように詰め込まれているような場合です。SUBSTR 関数は、このような固定長フィールドからのデータ抽出に最適です。

例えば、data_line というカラムに 'ABCDEFGHIJ01234KLMNO' という文字列が入っており、最初の10文字がフィールドA、次の5文字がフィールドB、その後の5文字がフィールドCを表しているとします。

sql
SELECT
data_line,
SUBSTR(data_line, 1, 10) AS field_a, -- 1文字目から10文字
SUBSTR(data_line, 11, 5) AS field_b, -- 11文字目から5文字
SUBSTR(data_line, 16, 5) AS field_c -- 16文字目から5文字
FROM
legacy_data;

解説:
* 各フィールドの開始位置と長さが分かっていれば、SUBSTR(string, start_pos, length) 形式で正確にデータを抽出できます。
* この方法は、データのフォーマットが厳密に固定されていることが前提となります。

4.6. 文字列の最初のN文字、最後のN文字を取得する

文字列の先頭や末尾から特定の文字数だけを取得したい場合、SUBSTR 関数を使用することも可能ですが、LEFT 関数と RIGHT 関数を使用する方が、その意図が明確になり、コードの可読性が向上します。しかし、SUBSTR でも同じことができます。

先頭からN文字:
SUBSTR(str, 1, N) は、LEFT(str, N) と同じ結果になります。

sql
SELECT
string,
SUBSTR(string, 1, 5) AS first_5_substr,
LEFT(string, 5) AS first_5_left
FROM
sample_strings;

末尾からN文字:
SUBSTR(str, -N) または SUBSTR(str, CHAR_LENGTH(str) - N + 1) は、RIGHT(str, N) と同じ結果になります。負のインデックスを使う方法がより簡潔です。

sql
SELECT
string,
SUBSTR(string, -5) AS last_5_substr_neg,
-- SUBSTR(string, CHAR_LENGTH(string) - 5 + 1) AS last_5_substr_pos, -- こちらでも可能だが複雑
RIGHT(string, 5) AS last_5_right
FROM
sample_strings;

解説:
* LEFT(str, N) は SUBSTR(str, 1, N) と等価です。
* RIGHT(str, N) は SUBSTR(str, -N) と等価です。
* 特定の目的（先頭から、末尾から）が明確な場合は、LEFT や RIGHT を使う方がコードの意図が伝わりやすいでしょう。ただし、SUBSTR の負のインデックス機能を使えば、RIGHT と同じ結果をシンプルに得られます。

4.7. 文字列の一部を別の文字列に置換する

SUBSTR 関数は文字列を抽出する関数ですが、これを CONCAT 関数などと組み合わせることで、文字列の一部を別の文字列に置換するような操作も実現できます。ただし、MySQLには直接置換を行う REPLACE 関数や、より柔軟な INSERT 関数（SQL標準のINSERTとは異なる文字列関数）が存在するため、通常はそちらを使います。しかし、SUBSTR を使った方法も理解しておくと、他の関数で対応できない特殊なケースや、関数がない環境での代替手段として役立つことがあります。

例：文字列 'abcde' の ‘cde’ 部分を ‘xyz’ に置換して ‘abxyz’ にしたい場合。

“`sql
— REPLACE 関数を使う方法（推奨）
SELECT REPLACE(‘abcde’, ‘cde’, ‘xyz’); — 結果: ‘abxyz’

— SUBSTR と CONCAT を組み合わせる方法
SELECT CONCAT(SUBSTR(‘abcde’, 1, 2), ‘xyz’); — 結果: ‘abxyz’
— ‘abcde’ の最初の2文字 (‘ab’) と ‘xyz’ を連結

— ある位置からある位置までを置換したい場合
— 例: ‘abcdefg’ の 3文字目から2文字 (‘cd’) を ‘XYZ’ に置換 -> ‘abXYZefg’
— REPLACE(‘abcdefg’, ‘cd’, ‘XYZ’) — これでも可能だが、’cd’ が複数あると全て置換される
— SUBSTR と CONCAT を組み合わせる方法
SELECT CONCAT(SUBSTR(‘abcdefg’, 1, 2), ‘XYZ’, SUBSTR(‘abcdefg’, 5));
— ‘abcdefg’ の最初の2文字 (‘ab’)
— ‘XYZ’ (置換したい文字列)
— ‘abcdefg’ の 5文字目から最後まで (‘efg’)
— これらを連結 -> ‘ab’ + ‘XYZ’ + ‘efg’ = ‘abXYZefg’
“`

解説:
* REPLACE(str, from_str, to_str) は、str 内の from_str を全て to_str に置換します。これは最も簡単な方法です。
* SUBSTR と CONCAT を組み合わせる方法は、「置換したい部分より前の部分」と「置換後の文字列」と「置換したい部分より後ろの部分」をそれぞれ取得し、連結することで実現します。これは、特定の開始位置と長さの部分だけを置換したい場合に有効です。ただし、INSERT(str, pos, len, newstr) 関数（MySQLの文字列関数）を使う方がより直感的です。
sql
-- INSERT 関数を使う方法
SELECT INSERT('abcdefg', 3, 2, 'XYZ'); -- 結果: 'abXYZefg'
-- 'abcdefg' の 3文字目から2文字を削除し、そこに 'XYZ' を挿入

このように、SUBSTR は抽出だけでなく、他の関数と組み合わせることで文字列の加工にも応用できますが、目的によっては REPLACE や INSERT といったより特化した関数が適している場合が多いです。

5. SUBSTR関数使用上の注意点

SUBSTR 関数を効果的に、そして正確に使用するためには、いくつかの重要な注意点があります。特に、インデックスの扱い方やマルチバイト文字の処理、パフォーマンスへの影響について理解しておくことが必要です。

5.1. インデックスは1から始まる

これは基本的な点ですが、他のプログラミング言語（多くは0から始まる）に慣れていると間違えやすいポイントです。MySQLの SUBSTR 関数の pos 引数は 1から始まります。

• SUBSTR(str, 1, ...) は最初の文字から開始
• SUBSTR(str, 0, ...) はMySQL 8.0以降では pos=0 の警告とともに空の文字列を返します（以前のバージョンではエラーになることもありました）。意図しない結果になるため、0を指定しないようにしましょう。

sql
SELECT SUBSTR('abc', 1, 1); -- 結果: 'a'
SELECT SUBSTR('abc', 2, 1); -- 結果: 'b'
SELECT SUBSTR('abc', 0, 1); -- 結果: '' (MySQL 8.0以降)

5.2. 負のインデックスの挙動

負のインデックス (pos < 0) は文字列の末尾から数えて開始位置を指定しますが、抽出自体はその位置から文字列の末尾に向かって行われます。

SUBSTR(str, -N) は、末尾からN番目の文字から文字列の最後までを抽出します。これは、RIGHT(str, N) とは異なります。RIGHT(str, N) は末尾のN文字を抽出します。

sql
SELECT SUBSTR('abcdefg', -3); -- 結果: 'efg' (末尾から3番目の'e'から最後まで)
SELECT RIGHT('abcdefg', 3); -- 結果: 'efg' (末尾の3文字)
-- この例では同じ結果ですが、
SELECT SUBSTR('abcdefg', -3, 2); -- 結果: 'ef' (末尾から3番目の'e'から2文字)
SELECT RIGHT('abcdefg', 2); -- 結果: 'fg' (末尾の2文字)
-- のように、len を指定すると結果が変わります。
負のインデックスを使う際は、len がどのように影響するかを正確に理解しておく必要があります。特に SUBSTR(str, -N, M) は、「末尾からN番目の文字から、M文字を抽出する」という意味になります。

5.3. マルチバイト文字（日本語など）の扱い

MySQLが正しく設定されていれば（例: character_set_server=utf8mb4, collation_server=utf8mb4_unicode_ci など）、SUBSTR 関数は文字単位で動作します。つまり、日本語の1文字は長さ1としてカウントされ、pos や len は文字数を指定することになります。

しかし、MySQLの文字コード設定が古い場合（例: latin1）や、カラムの文字コードが正しく設定されていない場合、SUBSTR がバイト単位で動作してしまう可能性があります。UTF-8では日本語1文字が3バイトや4バイトになるため、バイト単位で処理されると文字の途中で切り取られてしまい、文字化けや不正なデータになる恐れがあります。

確認方法:
* データベース、テーブル、カラムの文字コードを確認する (SHOW CREATE TABLE table_name;, SHOW VARIABLES LIKE 'character%';)
* CHAR_LENGTH() 関数を使って、文字列の文字長が正しく認識されているか確認する。LENGTH() 関数はバイト長を返します。

“`sql
SELECT
string,
CHAR_LENGTH(string) AS char_len, — 文字数
LENGTH(string) AS byte_len, — バイト数
SUBSTR(string, 1, 3) AS substr_chars — 3文字抽出（文字単位）
FROM
(SELECT ‘こんにちは’ AS string) AS t;

— 想定される結果 (UTF-8の場合)
— string | char_len | byte_len | substr_chars
— ——–|———-|———-|————–
— こんにちは | 5 | 15 | こんにち
``
もしSUBSTRがバイト単位で動作している場合、SUBSTR(‘こんにちは’, 1, 3)はこ` の最初の数バイトだけを返し、文字化けする可能性があります。

対策:
* MySQLのサーバー、データベース、テーブル、カラムの文字コード設定を utf8mb4 など、使用するマルチバイト文字に対応したものに正しく設定する。
* アプリケーションからの接続時の文字コードも適切に設定する。
* どうしてもバイト単位で処理したい場合は、SUBSTRING() の代わりに BINARY SUBSTRING() を使うか、あるいは文字コード変換関数を組み合わせるなどの方法がありますが、通常は文字単位の処理が望まれます。
* 文字長が必要な場合は、LENGTH() ではなく CHAR_LENGTH() を使うように注意する。

5.4. パフォーマンスに関する考慮事項

SUBSTR 関数を WHERE 句や ORDER BY 句に使用する場合、パフォーマンスに影響を与える可能性があります。

• インデックスの不使用: SUBSTR(column, ...) のようにカラムに対して関数を適用すると、通常そのカラムに作成されたインデックスが利用できなくなります（関数インデックスが利用可能な場合を除く）。これは、データベースがインデックスを使わずにテーブル全体のデータを読み込んで関数を適用する必要があるため、処理速度が著しく低下する可能性があります。
  sql
-- このクエリは name カラムのインデックスを利用できない可能性が高い
SELECT * FROM users WHERE SUBSTR(name, 1, 1) = 'A';
• 代替手段の検討:
  • もし可能であれば、抽出したい部分を別のカラムとして正規化して保持することを検討する。
  • WHERE 句で LIKE 'A%' のように、インデックスが利用できる条件を使う。
  • もし SUBSTR を使用する必要があるなら、結果を一時テーブルに格納してから処理する、あるいは検索条件を絞り込んだ後に SUBSTR を適用するなど、処理順序を工夫する。
  • MySQL 8.0以降の関数インデックスを利用することを検討する。例えば CREATE INDEX name_first_char_idx ON users ((SUBSTR(name, 1, 1))); のようにインデックスを作成すれば、WHERE SUBSTR(name, 1, 1) = 'A' のようなクエリでインデックスが利用される可能性があります。

大規模なテーブルに対して SUBSTR を WHERE 句に多用する場合は、パフォーマンスプロファイリングを行い、必要に応じて設計やクエリの見直しを行うことが重要です。

6. 関連する文字列操作関数

MySQLには SUBSTR 以外にも多くの文字列操作関数があります。SUBSTR を理解する上で、関連する関数を知っておくと、用途に応じて最適な関数を選択できるようになります。

• LEFT(str, len): 文字列 str の左端から len 文字を抽出します。SUBSTR(str, 1, len) と同じです。先頭から抽出したい場合に、より意図が明確になります。
• RIGHT(str, len): 文字列 str の右端から len 文字を抽出します。SUBSTR(str, -len) と同じ結果になります。末尾から抽出したい場合に、より意図が明確になります。
• LENGTH(str): 文字列 str のバイト長を返します。マルチバイト文字の場合、文字数とバイト数は異なります。
• CHAR_LENGTH(str) / CHARACTER_LENGTH(str): 文字列 str の文字数を返します。マルチバイト文字を扱う際には、この関数で文字長を取得することが重要です。
• LOCATE(substring, string, [pos]): 文字列 string の中で substring が最初に現れる位置を返します。オプションの pos を指定すると、その位置から検索を開始します。見つからない場合は 0 を返します。SUBSTR と組み合わせて、区切り文字などを利用した抽出によく使われます。
• INSTR(string, substring): LOCATE(substring, string) と同じ機能です。引数の順番が逆です。
• POSITION(substring IN string): LOCATE(substring, string) と同じ機能で、SQL標準の構文です。
• MID(str, pos, [len]): SUBSTRING(str, pos, [len]) と同じ機能です。SUBSTR, SUBSTRING, MID は同じシノニムです。
• TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str): 文字列の先頭、末尾、または両端から、指定した文字列（デフォルトは空白）を除去します。抽出した部分文字列の前後に不要な空白がある場合などに、SUBSTR の結果に対して適用することがあります。
• REPLACE(str, from_str, to_str): 文字列 str 内に含まれる from_str を全て to_str に置換します。部分置換の多くのケースで SUBSTR よりも適しています。
• INSERT(str, pos, len, newstr): 文字列 str の pos の位置から len 文字を削除し、その位置に newstr を挿入します。特定の位置と長さの部分を別の文字列に置き換えたい場合に SUBSTR + CONCAT よりも適しています。
• SUBSTRING_INDEX(str, delim, count): 文字列 str を区切り文字 delim で分割し、count 番目の区切り文字までの部分を返します。count が正の場合は先頭から、負の場合は末尾から数えます。ファイル名やドメイン名抽出など、区切り文字に基づいた抽出に非常に便利で、SUBSTR と LOCATE を組み合わせるよりもシンプルになることが多いです。

これらの関数は、それぞれ異なる得意分野を持っています。解決したい問題に応じて、適切な関数を選択することが重要です。特に、位置や長さが固定されている場合は SUBSTR、区切り文字に基づいて抽出したい場合は LOCATE/INSTR/POSITION や SUBSTRING_INDEX、先頭や末尾が決まっている場合は LEFT/RIGHT、といった使い分けが一般的です。

7. SUBSTR関数と他の関数を組み合わせた高度な使い方

SUBSTR 関数は、他の関数と組み合わせることで、より複雑な文字列パターンからのデータ抽出や加工が可能になります。ここでは、特によく使われる LOCATE/INSTR や SUBSTRING_INDEX との組み合わせに焦点を当てて説明します。

7.1. LOCATE/INSTR と SUBSTR を組み合わせて、特定の区切り文字に基づいた部分文字列抽出

区切り文字で区切られた文字列から、特定の要素を取得したい場合によく使われるパターンです。

例：カンマ区切りの文字列からN番目の要素を取得する

例えば、'apple,banana,orange,grape' という文字列から、2番目の要素である 'banana' を取得したいとします。

1. 1番目のカンマの位置を見つける。
2. その位置の次から検索を開始して、2番目のカンマの位置を見つける。
3. 1番目のカンマの次の位置から、2番目のカンマの直前までの文字列を抽出する。

これは、LOCATE 関数を複数回使用して実現できます。

“`sql
SELECT
string,
— 2番目の要素を取得 (‘banana’)
SUBSTR(string, LOCATE(‘,’, string) + 1, LOCATE(‘,’, string, LOCATE(‘,’, string) + 1) – (LOCATE(‘,’, string) + 1)) AS second_element
FROM
(SELECT ‘apple,banana,orange,grape’ AS string) AS t;

— 解説:
— LOCATE(‘,’, string) -> 6 (1番目のカンマの位置)
— LOCATE(‘,’, string) + 1 -> 7 (1番目のカンマの次の位置、’b’)
— SUBSTR(string, 7, …) -> 7番目から抽出開始
— LOCATE(‘,’, string, LOCATE(‘,’, string) + 1)
— LOCATE(‘,’, string, 7) -> 13 (7番目から検索して最初に見つかるカンマ、つまり2番目のカンマの位置)
— 13 – (LOCATE(‘,’, string) + 1)
— 13 – 7 -> 6 (抽出する長さ、’banana’ は6文字)
— SUBSTR(string, 7, 6) -> ‘banana’
“`

この方法は、N番目の要素を取得する際に LOCATE を N回使う必要があり、非常に複雑になりがちです。このようなケースでは、やはり SUBSTRING_INDEX 関数を使う方が圧倒的に簡単です。

“`sql
— SUBSTRING_INDEX を使う方法 (推奨)
SELECT
string,
SUBSTRING_INDEX(SUBSTRING_INDEX(string, ‘,’, 2), ‘,’, -1) AS second_element_simple
FROM
(SELECT ‘apple,banana,orange,grape’ AS string) AS t;

— 解説:
— SUBSTRING_INDEX(string, ‘,’, 2) -> 2番目のカンマまでを取得 -> ‘apple,banana’
— SUBSTRING_INDEX(‘apple,banana’, ‘,’, -1) -> ‘apple,banana’ を ‘,’ で分割し、末尾から1番目の要素を取得 -> ‘banana’
“`

例：ファイルパスから拡張子を除いた部分を取得する

ファイルパスから末尾の .txt や .jpg といった拡張子を除いた部分を取得したい場合です。末尾の . の位置を見つけて、その手前までを抽出します。末尾の . は REVERSE と LOCATE を組み合わせるか、SUBSTRING_INDEX で -1 番目の区切り文字までを取得するという方法があります。

SUBSTRING_INDEX で末尾の . の手前までを取得するのが最も簡単です。

“`sql
SELECT
filepath,
SUBSTRING_INDEX(filepath, ‘.’, 1) AS base_name — 最初の ‘.’ までを取得
FROM
(SELECT ‘document.txt’ AS filepath) AS t;
— 結果: ‘document’

SELECT
filepath,
SUBSTRING_INDEX(filepath, ‘.’, -1) AS extension — 最後の ‘.’ 以降を取得
FROM
(SELECT ‘archive.tar.gz’ AS filepath) AS t;
— 結果: ‘gz’

SELECT
filepath,
— 拡張子を除いた部分を取得 (最後の ‘.’ の手前まで)
— 全長 – (最後の ‘.’ の位置を含む末尾からの文字数) = 最後の ‘.’ の位置
— 最後の ‘.’ の位置を見つける
— LOCATE(‘.’, REVERSE(filepath)) は末尾から数えて最初の ‘.’ の位置
— 全長 – 末尾からの ‘.’ の位置 + 1 が、先頭から数えた ‘.’ の位置
SUBSTR(filepath, 1, LENGTH(filepath) – LOCATE(‘.’, REVERSE(filepath))) AS base_name_no_ext
FROM
(SELECT ‘archive.tar.gz’ AS filepath) AS t
WHERE LOCATE(‘.’, filepath) > 0; — 拡張子がない場合は除外または別途処理
``
**解説:**
*SUBSTRING_INDEX(filepath, ‘.’, 1)は、拡張子が1つしかない場合は有効ですが、archive.tar.gzのように複数の.が含まれる場合は最初の.までのarchiveを返してしまい、意図しない結果になることがあります。
* 拡張子を除いた部分を取得するには、文字列の全長から、末尾の.の位置までの長さを引く必要があります。末尾の.の位置を特定するために、文字列を反転 (REVERSE) させてからLOCATEで.の位置を探し、その情報を利用します。LOCATE(‘.’, REVERSE(filepath))は、反転した文字列での.の位置、すなわち元の文字列における末尾からの.の位置です。LENGTH(filepath) – LOCATE(‘.’, REVERSE(filepath))は、末尾の.の *直前* までの長さを計算しています。SUBSTR(filepath, 1, 計算した長さ)` で先頭からその長さだけ抽出します。

この例でも分かるように、SUBSTRING_INDEX を適切に使う方がシンプルになるケースが多いですが、SUBSTR と LOCATE/REVERSE を組み合わせることで、より低レベルな文字列位置の計算に基づいた抽出も可能です。

7.2. SUBSTR と CASE 文を使った条件付き抽出

CASE 文と SUBSTR を組み合わせることで、特定の条件に基づいて異なる部分文字列を抽出したり、データの形式が混在している場合に対応したりできます。

例：データのフォーマットが異なる場合に対応する

例えば、商品コードが 'ABC-12345' または 'XYZ987' のように、プレフィックスが - で区切られているものと、区切り文字がないものが混在しているとします。プレフィックス以降の番号部分を抽出したい場合。

sql
SELECT
product_code,
CASE
WHEN product_code LIKE '%-%' THEN SUBSTR(product_code, LOCATE('-', product_code) + 1) -- '-' がある場合、その次から抽出
ELSE SUBSTR(product_code, 4) -- '-' がない場合、先頭4文字がプレフィックスと仮定して5文字目から抽出
END AS item_number
FROM
products;

解説:
* CASE WHEN product_code LIKE '%-%' で、商品コードに - が含まれているかどうかを判定します。
* - が含まれている場合は、LOCATE('-', product_code) + 1 を開始位置として、- の後ろの部分を抽出します。
* - が含まれていない場合は、別のルール（この例では先頭4文字がプレフィックス）に基づいて、SUBSTR(product_code, 4) で5文字目以降を抽出します。
* このように CASE 文を使うことで、異なるフォーマットのデータに対して、それぞれ適切な方法で部分文字列を抽出できます。

7.3. SUBSTR と UPDATE 文、INSERT 文を組み合わせたデータ操作

SUBSTR は抽出関数ですが、その抽出結果を他の関数と組み合わせて、データの更新や挿入を行うこともあります。

例：カラムの一部を更新する

例えば、item_code というカラムの先頭3文字だけを別の値に更新したい場合などです。これは UPDATE 文の中で SUBSTR (抽出) と CONCAT (連結) を組み合わせて実現できます。

sql
-- item_code が 'OLD123' のものを 'NEW123' に更新したい
UPDATE products
SET item_code = CONCAT('NEW', SUBSTR(item_code, 4)) -- 新しいプレフィックス ('NEW') と、元のコードの4文字目以降を連結
WHERE SUBSTR(item_code, 1, 3) = 'OLD'; -- 先頭3文字が 'OLD' のデータを対象にする

解説:
* WHERE SUBSTR(item_code, 1, 3) = 'OLD' で、更新対象の行を絞り込みます。この条件句での SUBSTR 利用は、前述の通りインデックス利用の点で注意が必要ですが、対象行が少ない場合や他に効率的な条件がある場合は問題にならないことがあります。
* SET item_code = CONCAT('NEW', SUBSTR(item_code, 4)) で更新値を生成します。SUBSTR(item_code, 4) で元の item_code の4文字目以降（123 部分）を抽出し、それに新しいプレフィックス 'NEW' を連結しています。

例：抽出したデータを別のテーブルに挿入する

例えば、raw_data テーブルの特定のカラムから SUBSTR で部分文字列を抽出し、その抽出結果を processed_data テーブルの別のカラムに挿入する場合などです。

sql
INSERT INTO processed_data (id, year, month, day)
SELECT
id,
SUBSTR(date_string, 1, 4) AS year,
SUBSTR(date_string, 6, 2) AS month,
SUBSTR(date_string, 9, 2) AS day
FROM
raw_data
WHERE
date_string LIKE '____-__-__'; -- 有効なフォーマットのデータのみ対象

解説:
* INSERT ... SELECT 構文を使って、raw_data テーブルからデータを読み込み、processed_data テーブルに挿入しています。
* SELECT 文の中で SUBSTR 関数を使って、raw_data テーブルの date_string カラムから年、月、日をそれぞれ抽出し、それを processed_data テーブルの対応するカラムにマッピングしています。

このように、SUBSTR 関数は単にデータを閲覧するためだけでなく、データの加工や変換を伴う UPDATE や INSERT などのデータ操作にも広く利用されます。

8. 具体的なシナリオ別の例（より実践的に）

ここでは、実際のデータベース操作を想定した、より具体的なシナリオにおける SUBSTR 関数の利用例を見ていきます。

8.1. ユーザーテーブルから、特定の市区町村コードを持つ住所データを抽出し、市区町村名部分だけを表示する

ユーザーテーブルに address カラムがあり、ここに '東京都千代田区丸の内1-1-1' のような住所が格納されているとします。住所の先頭部分に市区町村名が含まれていると仮定し、特定の市区町村名を持つユーザーを検索し、抽出した市区町村名を表示したい場合。

ただし、住所フォーマットは完璧ではなく、前後の空白や表記揺れがある可能性も考慮すると、単純な SUBSTR だけでは難しい場合があります。ここでは、比較的規則的なデータを前提とし、さらに TRIM 関数で前後の空白を取り除く処理も組み合わせます。特定の市区町村で始まる住所を LIKE で検索し、その中から市区町村名部分を抽出します。市区町村名が固定長でないため、区切り文字（この例では「区」や「市」の最後の文字）を利用します。

“`sql
— users テーブル構造例:
— id INT PRIMARY KEY,
— name VARCHAR(100),
— address VARCHAR(255)

— 特定の市区町村（例: ‘東京都千代田区’ または ‘大阪市中央区’）のユーザーを検索し、市区町村名を表示
SELECT
id,
name,
address,
— ‘東京都千代田区’ や ‘大阪市中央区’ など、区で終わる住所の場合
— ‘東京都千代田区’ の長さは 7文字
— ‘大阪市中央区’ の長さは 6文字
— 区切り文字が固定でないため、区切り文字の位置で判断
— ここでは仮に「区」または「市」で終わるものを想定
TRIM(SUBSTR(address, 1, LOCATE(‘区’, address) + 1)) AS city_ward_ku, — 「区」までを抽出
TRIM(SUBSTR(address, 1, LOCATE(‘市’, address) + 1)) AS city_shi — 「市」までを抽出
FROM
users
WHERE
address LIKE ‘東京都千代田区%’ OR address LIKE ‘大阪市中央区%’; — 検索条件例

— もう少し汎用的に、都道府県名+市区町村名の部分を抽出したい場合
— 都道府県名の後ろにある「都」「道」「府」「県」の次から、その後の「市」「区」「町」「村」の次までの部分？
— これは非常に複雑になるため、住所データを分解して保持するのが理想的です。
— SUBSTRING_INDEX を使う方法（「県」「都」「道」「府」の次から、最後の「市」「区」「町」「村」までを区切り文字で取得）
— 例えば「東京都千代田区」なら、「都」の次から最後の区切り文字「区」まで
— これは単一の関数では難しい。正規表現が使える環境なら REGEXP_SUBSTR が強力。
— SUBSTR と LOCATE の組み合わせ例：都道府県名の後ろの区切り文字を探し、そこから最初の「市」「区」「町」「村」までを抽出
SELECT
address,
CASE
WHEN address LIKE ‘東京都%’ THEN SUBSTR(address, 4, LOCATE(‘区’, address, 4) – 4 + 1) — 東京(都)の後ろ4文字目から「区」まで
WHEN address LIKE ‘大阪府%’ THEN SUBSTR(address, 4, LOCATE(‘市’, address, 4) – 4 + 1) — 大阪(府)の後ろ4文字目から「市」まで
— … 他の都道府県パターン …
ELSE NULL — または他の処理
END AS city_ward_extract
FROM
users
WHERE
address IS NOT NULL;
“`

解説:
* 住所のような非構造化データから特定の要素を正確に抽出するのは、フォーマットの揺れや表記の違いがあるため一般的に難しい課題です。理想的には、住所を都道府県、市区町村、番地などに分解して別途カラムに格納すべきです。
* しかし、もし文字列として格納されている場合に SUBSTR を使うとすれば、区切り文字や固定位置に頼ることになります。
* 上記の例では、LOCATE を使って区切り文字（この場合は「区」や「市」の最後の文字）の位置を見つけ、SUBSTR でその位置までの部分を抽出しています。
* TRIM 関数は、抽出結果の前後に不要な空白が含まれる可能性を考慮して追加しています。
* CASE 文は、都道府県によって区切り文字の位置や抽出ルールが異なる場合に対応するために利用できます。

このような複雑なパターンマッチングや抽出には、SUBSTR と LOCATE だけでは限界があり、正規表現関数（REGEXP_SUBSTR, REGEXP_INSTR など）が利用できる場合は、そちらの方が柔軟かつ強力です。しかし、SUBSTR と他の関数を組み合わせることで、ある程度のパターンには対応できることを示しています。

8.2. 商品コード（例: ABC-12345-X）から、カテゴリコード（ABC）、製品番号（12345）、バリアント（X）を分解して取得する

区切り文字（-）が複数回出現する文字列から、各要素を抽出する例です。SUBSTRING_INDEX を使うのが最も適していますが、SUBSTR と LOCATE を組み合わせて実現することも可能です。

sql
-- product_code のフォーマット: CATEGORY-PRODUCT_NUMBER-VARIANT
SELECT
product_code,
-- カテゴリコード: 最初の '-' の手前まで
SUBSTR(product_code, 1, LOCATE('-', product_code) - 1) AS category,
-- 製品番号: 最初の '-' の次から、2番目の '-' の手前まで
SUBSTR(
product_code, -- 元の文字列
LOCATE('-', product_code) + 1, -- 開始位置: 最初の '-' の次
LOCATE('-', product_code, LOCATE('-', product_code) + 1) -- 2番目の '-' の位置
- (LOCATE('-', product_code) + 1) -- 長さ: 2番目の '-' の位置から開始位置を引く
) AS product_number,
-- バリアント: 最後の '-' の次から最後まで (負のインデックスを使うか、SUBSTRING_INDEX を使うのが楽)
-- SUBSTR(product_code, LOCATE('-', product_code, LOCATE('-', product_code) + 1) + 1) AS variant_complex -- 複雑
SUBSTRING_INDEX(product_code, '-', -1) AS variant_simple -- SUBSTRING_INDEX 推奨
FROM
products
WHERE
product_code LIKE '%-%-%'; -- フォーマットに合うものを対象

解説:
* カテゴリコードは最初の - の手前なので、SUBSTR(str, 1, LOCATE('-', str) - 1) で取得できます。
* 製品番号は最初の - の次から2番目の - の手前までです。開始位置は LOCATE('-', str) + 1、長さは「2番目の - の位置」から「開始位置」を引くことで計算できます。2番目の - の位置は LOCATE('-', str, LOCATE('-', str) + 1) のように、最初の - の次から検索を開始することで見つけます。
* バリアントは最後の - の次から最後までです。これは SUBSTRING_INDEX(str, '-', -1) を使うのが最も簡潔です。SUBSTR と LOCATE を組み合わせる場合、2番目の - の位置（つまり最後の - の位置）を見つけて、その次から最後までを抽出することになりますが、コードが複雑になります。

この例は、SUBSTR と LOCATE を組み合わせることで、複数の区切り文字による分解が可能であることを示していますが、このような多段階の分解には SUBSTRING_INDEX を繰り返し適用する方がより読みやすく、効率的な場合が多いです。

8.3. ログデータから、特定のタイムスタンプ形式の文字列の一部（例: 時刻だけ）を抽出する

ログデータなどに 'YYYY-MM-DD HH:MI:SS' のような固定フォーマットのタイムスタンプが文字列として記録されている場合、時刻部分（HH:MI:SS）だけを抽出したいことがあります。

“`sql
— logs テーブル構造例:
— id INT PRIMARY KEY,
— message TEXT,
— log_timestamp_str VARCHAR(50) — 例: ‘2023-10-27 10:30:00’

SELECT
id,
message,
log_timestamp_str,
— 時刻部分 ‘HH:MI:SS’ を抽出 (12文字目から8文字)
SUBSTR(log_timestamp_str, 12, 8) AS log_time
FROM
logs
WHERE
log_timestamp_str IS NOT NULL AND CHAR_LENGTH(log_timestamp_str) >= 19; — フォーマット長を仮定
“`

解説:
* 'YYYY-MM-DD HH:MI:SS' というフォーマットは固定長（19文字）であり、時刻部分は12文字目から始まる8文字です。
* SUBSTR(log_timestamp_str, 12, 8) を使うことで、正確に HH:MI:SS の部分を抽出できます。
* WHERE CHAR_LENGTH(log_timestamp_str) >= 19 は、抽出処理を行う前に、対象の文字列が少なくとも必要な長さを満たしているかを確認するための条件です。

このように、固定長または固定位置にデータがある場合は、SUBSTR 関数が非常にシンプルかつ効率的な抽出手段となります。

9. まとめ

この記事では、MySQLで文字列から部分文字列を抽出する SUBSTR 関数（およびそのシノニム SUBSTRING）について、その基本的な使い方から応用、注意点まで詳しく解説しました。

SUBSTR 関数は、SUBSTR(str, pos) または SUBSTR(str, pos, len) というシンプルな構文を持ちながら、pos と len の組み合わせによって、文字列の任意の部分を柔軟に抽出できる強力なツールです。特に、インデックスが1から始まること、負のインデックスが末尾からの位置を指定すること、そして len が残りの文字列長を超える場合は末尾まで抽出されるという挙動は、正確な抽出を行う上で重要です。

また、日本語のようなマルチバイト文字を扱う際には、MySQLの文字コード設定が正しく行われていることを確認し、SUBSTR が文字単位で動作することを確認することが不可欠です。CHAR_LENGTH() と LENGTH() の違いを理解しておくことも重要です。

単独での利用だけでなく、LOCATE/INSTR、SUBSTRING_INDEX、REPLACE、CONCAT、TRIM、CASE といった他の文字列操作関数と組み合わせることで、より複雑なパターンからの抽出や、文字列の加工、さらには UPDATE や INSERT といったデータ操作にも応用できることを具体的な例で示しました。特に、区切り文字による分解には SUBSTRING_INDEX が、特定部分の置換には REPLACE や INSERT が便利な場合が多いことも触れました。

ただし、SUBSTR 関数を WHERE 句でカラムに適用すると、インデックスが効かなくなりパフォーマンスが低下する可能性があるという注意点も理解しておく必要があります。大規模なデータを扱う際には、代替手段（正規化、LIKE 句、関数インデックスなど）の検討が推奨されます。

MySQLにおけるデータ操作において、文字列操作は避けて通れない重要な技術です。SUBSTR 関数は、その中でも基本中の基本であり、これをマスターすることは、MySQLを使った開発やデータ分析の幅を大きく広げます。ぜひ、この記事で学んだことを活かして、実際のクエリ作成に挑戦してみてください。

10. 付録/FAQ

Q: SUBSTR と SUBSTRING の違いは？

A: MySQLにおいては、SUBSTR と SUBSTRING は完全に同じ機能を持つシノニムです。どちらを使っても結果は変わりません。SQL標準では SUBSTRING が一般的ですが、MySQLではどちらもサポートされています。

Q: インデックスを0から始めたいんだけど？

A: MySQLの文字列関数のインデックスは1から始まります。他の言語のように0から開始するオプションはありません。もし0ベースのインデックスで考えたい場合は、アプリケーション側で処理するか、抽出したい位置から1を引いて pos 引数に指定する必要があります（例: 0文字目から3文字 -> SUBSTR(str, 1, 3)、2文字目から5文字 -> SUBSTR(str, 3, 5)）。MySQLの仕様として1ベースで考えるのが最も自然です。

Q: 日本語が正しく切り取れない（文字化けする）場合は？

A: これは主にMySQLの文字コード設定の問題である可能性が高いです。SUBSTR 関数がバイト単位で処理されているのかもしれません。以下の点を確認してください。
1. MySQLサーバー、データベース、テーブル、カラムの文字コード設定: utf8mb4 など、日本語を正しく扱える設定になっているか確認します (SHOW VARIABLES LIKE 'character%';, SHOW CREATE TABLE table_name;)。
2. クライアント接続時の文字コード: アプリケーションからMySQLに接続する際の文字コードも重要です。正しく設定されていないと、サーバー側がUTF-8でも、クライアントとの間で文字化けが発生することがあります。SET NAMES 'utf8mb4'; のようなコマンドで接続文字コードを設定できます。
3. CHAR_LENGTH() の確認: SELECT CHAR_LENGTH('こんにちは'); を実行し、文字数（例: 5）が正しく返されるか確認します。SELECT LENGTH('こんにちは'); がバイト数（例: 15 または 20）を返すのが正しい挙動です。
これらの設定が正しく行われていれば、SUBSTR は文字単位で動作するはずです。

Q: SUBSTR を WHERE 句で使うと遅いって聞いたけど？

A: はい、一般的にカラムに対して関数を適用すると、そのカラムに作成されたインデックスが利用されにくくなります。WHERE SUBSTR(column, ...) = '...' のような条件は、データベースがテーブルの全行を読み込んでから SUBSTR を実行し、その結果を条件と比較するため、非常に遅くなる可能性があります。
対策としては、以下のようなものがあります。
* 可能であれば、LIKE 'prefix%' のように、インデックスが利用できる条件に書き換える。
* 抽出したい部分を別のカラムとして保持する（正規化）。
* MySQL 8.0以降で利用可能な関数インデックスを作成する。
* 検索条件を絞り込んだ後に SUBSTR を適用するなど、クエリの構成を見直す。

Q: SUBSTR(str, pos) と SUBSTR(str, pos, len) の違いは？

A:
* SUBSTR(str, pos) は、開始位置 pos から文字列の 最後まで を抽出します。
* SUBSTR(str, pos, len) は、開始位置 pos から 指定した長さ len だけ を抽出します。
長さを限定したい場合は len を指定し、末尾まで取得したい場合は len を省略します。

---