MySQL SUBSTR関数とは？文字列操作の基本をマスター

---

MySQL SUBSTR関数とは？文字列操作の基本をマスター

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

データベース、特にリレーショナルデータベースであるMySQLにおいて、データの格納、管理、そして分析は中心的なタスクです。データは数値、日付、論理値など様々な形式で存在しますが、最も一般的で柔軟性の高い形式の一つが「文字列」です。氏名、住所、商品の説明、URL、ログ情報など、私たちの周りにある情報の多くは文字列として表現されます。

しかし、文字列データは単に格納されているだけでは価値を発揮しません。特定の情報を取り出したり、不要な部分を除去したり、他のデータと結合したり、フォーマットを整えたりといった「文字列操作」が必要不可欠です。例えば、長い商品説明の中から最初の数十字だけを抜き出してリスト表示したい、電話番号の市外局番だけを取り出したい、メールアドレスのドメイン部分を抽出したい、といった場合に文字列操作関数が活躍します。

MySQLは、強力で多機能な文字列操作関数を数多く提供しています。その中でも、最も基本的な操作の一つである「部分文字列の抽出」を行うのが、今回詳しく解説するSUBSTR関数です。SUBSTR関数は、指定した文字列の中から、特定の位置から指定した長さだけ文字を取り出すことができます。この関数をマスターすることは、MySQLでのデータ操作の幅を大きく広げるための第一歩と言えるでしょう。

この記事では、MySQLのSUBSTR関数の基本的な使い方から、詳細な挙動、様々な応用例、さらには他の関連関数との組み合わせまで、徹底的に解説します。約5000語というボリュームで、SUBSTR関数に関するあらゆる側面を網羅し、読者の皆様がこの関数を完全にマスターできるよう、丁寧に進めていきます。

1. SUBSTR関数とは？基本を理解する

SUBSTR関数（またはSUBSTRING関数）は、MySQLにおいて文字列から部分文字列を抽出するために使用される関数です。非常にシンプルながら、文字列操作の多くの場面で基礎となります。

1.1. SUBSTR関数の構文

SUBSTR関数には、主に二つの構文があります。

構文1：開始位置と長さを指定

sql
SUBSTR(str, pos, len)

構文2：開始位置から文字列の最後までを指定

sql
SUBSTR(str, pos)

どちらの構文も同じ機能を提供しており、抽出したい文字列と開始位置を指定します。構文1では、さらに抽出する文字数を指定できます。構文2は、指定した開始位置から文字列の末尾まで全てを抽出します。

また、MySQLではSUBSTRの他にSUBSTRINGやMIDという関数名も使用できますが、これらは全てSUBSTR関数のエイリアス（別名）です。つまり、機能は全く同じです。ドキュメントや他の人が書いたコードではこれらの別名を目にすることがありますが、機能的にはSUBSTRと同じと考えて差し支えありません。

1.2. 引数の説明

それぞれの引数について詳しく見ていきましょう。

• str:

  • 必須の引数です。
  • 部分文字列を抽出したい元の文字列を指定します。
  • テーブルのカラム名、文字列リテラル（例: 'Hello World'）、あるいは文字列を返す式などを指定できます。
  • もしstrがNULLの場合、SUBSTR関数はNULLを返します。

• pos:

  • 必須の引数です。
  • 部分文字列の抽出を開始する位置を指定します。
  • MySQLの文字列のインデックスは1から始まります。 多くのプログラミング言語（例: C, Java, Python）では0から始まることが多いですが、MySQLでは1が最初の文字です。
  • posに正の整数を指定した場合、文字列の先頭から数えてその位置から抽出が始まります。
  • posに負の整数を指定した場合、文字列の末尾から数えてその位置から抽出が始まります。例えば、-1は末尾の文字、-2は末尾から2番目の文字を指します。
  • posが文字列の長さを超える正の数の場合、結果は空文字列''になります。
  • posが文字列の長さを超える負の数の場合（例: 長さ5の文字列に対して-6）、抽出は文字列の先頭から行われ、posが1と見なされた場合と同じ挙動になります（ただし、指定されたlenの範囲で）。実際には、負の数の絶対値が文字列長より大きい場合、開始位置は常に文字列の最初の位置と見なされます。
  • posに0を指定した場合、結果は空文字列''になります。

• len:

  • オプションの引数です（構文1の場合）。
  • 抽出する部分文字列の長さを指定します。
  • 正の整数を指定します。
  • もしlenが省略された場合（構文2）、抽出はposで指定された位置から文字列の最後まで行われます。
  • もしlenが、開始位置(pos)から文字列の末尾までの残りの文字数より大きい場合、抽出は文字列の末尾まで行われます。つまり、指定されたlenの長さに関わらず、文字列の範囲を超える部分は抽出されません。
  • もしlenに負の数を指定した場合、結果は空文字列''になります。
  • もしlenに0を指定した場合、結果は空文字列''になります。

これらの引数の挙動、特にposの1始まりや負の値の扱い、lenの省略や無効な値の挙動を理解することが、SUBSTR関数を正確に使う上で非常に重要です。

1.3. 簡単な使用例

まずは最も基本的な例を見てみましょう。

例1：文字列の先頭から数文字を抽出

sql
SELECT SUBSTR('abcdefg', 1, 3);

• 元の文字列: 'abcdefg'
• 開始位置 (pos): 1 (先頭から)
• 長さ (len): 3 (3文字)

結果：
+-------------------------+
| SUBSTR('abcdefg', 1, 3) |
+-------------------------+
| abc |
+-------------------------+

これは「abcdefgの1文字目から3文字取り出す」という意味になり、結果は'abc'になります。

例2：文字列の途中から数文字を抽出

sql
SELECT SUBSTR('abcdefg', 3, 4);

• 元の文字列: 'abcdefg'
• 開始位置 (pos): 3 (先頭から3文字目)
• 長さ (len): 4 (4文字)

結果：
+-------------------------+
| SUBSTR('abcdefg', 3, 4) |
+-------------------------+
| cdef |
+-------------------------+

これは「abcdefgの3文字目から4文字取り出す」という意味になり、結果は'cdef'になります。

例3：文字列の途中から最後までを抽出（len省略）

sql
SELECT SUBSTR('abcdefg', 5);

• 元の文字列: 'abcdefg'
• 開始位置 (pos): 5 (先頭から5文字目)
• 長さ (len): 省略 (最後まで)

結果：
+--------------------+
| SUBSTR('abcdefg', 5) |
+--------------------+
| efg |
+--------------------+

これは「abcdefgの5文字目から最後まで取り出す」という意味になり、結果は'efg'になります。

例4：負の数を使った位置指定

sql
SELECT SUBSTR('abcdefg', -3, 2);

• 元の文字列: 'abcdefg'
• 開始位置 (pos): -3 (末尾から3文字目)
• 長さ (len): 2 (2文字)

結果：
+--------------------------+
| SUBSTR('abcdefg', -3, 2) |
+--------------------------+
| ef |
+--------------------------+

これは「abcdefgの末尾から3文字目の位置（つまりe）から、2文字取り出す」という意味になり、結果は'ef'になります。負の数を使うと、文字列の長さが事前に分からない場合でも、末尾からの位置を指定できるため便利です。

例5：lenが残りの文字数を超える場合

sql
SELECT SUBSTR('abcdefg', 5, 10);

• 元の文字列: 'abcdefg'
• 開始位置 (pos): 5 (先頭から5文字目)
• 長さ (len): 10 (10文字)

結果：
+--------------------------+
| SUBSTR('abcdefg', 5, 10) |
+--------------------------+
| efg |
+--------------------------+

5文字目(e)から末尾までの文字数は3文字です。指定された長さ10はこれより大きいですが、MySQLは文字列の範囲を超えて抽出することはありません。そのため、結果は5文字目から末尾までの'efg'になります。

例6：posが無効な場合

sql
SELECT SUBSTR('abcdefg', 10, 3); -- posが文字列長を超える

結果：
+--------------------------+
| SUBSTR('abcdefg', 10, 3) |
+--------------------------+
| |
+--------------------------+
開始位置10は文字列'abcdefg'の長さ7を超えています。この場合、結果は空文字列''になります。

sql
SELECT SUBSTR('abcdefg', 0, 3); -- posが0

結果：
+-------------------------+
| SUBSTR('abcdefg', 0, 3) |
+-------------------------+
| |
+-------------------------+
開始位置0は無効な位置です。結果は空文字列''になります。

sql
SELECT SUBSTR('abcdefg', 3, -2); -- lenが負の数

結果：
+--------------------------+
| SUBSTR('abcdefg', 3, -2) |
+--------------------------+
| |
+--------------------------+
長さ-2は無効な長さです。結果は空文字列''になります。

これらの基本的な例を通して、SUBSTR関数の引数がどのように結果に影響するかを理解できたかと思います。特に、posの1始まりと負の数の扱い、そしてlenの挙動は、後述する応用例を理解する上で非常に重要です。

2. SUBSTR関数の詳細な挙動と落とし穴

SUBSTR関数はシンプルですが、いくつかの詳細な挙動や注意点があります。これらを把握しておくことで、予期せぬ結果を防ぎ、より正確な文字列操作が可能になります。

2.1. 1始まりのインデックス

前述の通り、MySQLの文字列関数は基本的に1始まりのインデックスを使用します。これは、多くのプログラミング言語（Python, Java, C++, JavaScriptなど）が0始まりであることと異なります。この違いを理解せずに関数を使用すると、オフバイワンエラー（位置が1つずれるエラー）を引き起こしやすいので注意が必要です。

例えば、「最初の文字」を取りたい場合、posは1を指定します。

sql
SELECT SUBSTR('MySQL', 1, 1); -- 結果: 'M'

もし0を指定すると、空文字列になります。

sql
SELECT SUBSTR('MySQL', 0, 1); -- 結果: ''

常に1始まりであることを意識しましょう。

2.2. 負の数の位置指定の詳細

posに負の数を指定すると、文字列の末尾から数えた位置を開始点とすることができます。これは、文字列の長さが不定である場合に特に便利です。

sql
SELECT SUBSTR('abcdefg', -1); -- 末尾の文字から最後まで -> g
SELECT SUBSTR('abcdefg', -2); -- 末尾から2番目の文字から最後まで -> fg
SELECT SUBSTR('abcdefg', -3, 1); -- 末尾から3番目の文字から1文字 -> e
SELECT SUBSTR('abcdefg', -5, 3); -- 末尾から5番目の文字から3文字 -> cde

負の数を指定する際の注意点として、その絶対値が文字列の長さより大きい場合です。

sql
SELECT LENGTH('abc'); -- 結果: 3
SELECT SUBSTR('abc', -4, 2); -- 末尾から4文字目？ ('a'の前)

この場合、開始位置が文字列の範囲外になります。MySQLは、負の数の絶対値が文字列長より大きい場合、開始位置を文字列の先頭（1文字目）と見なして抽出を行います。ただし、これは少し直感的でない挙動かもしれません。上記の例では、-4の絶対値は4で、文字列長3より大きいです。この場合、posは1と見なされ、SUBSTR('abc', 1, 2)が実行されたかのような結果になります。

sql
SELECT SUBSTR('abc', -4, 2); -- 結果: 'ab'

しかし、これは常にそうなるわけではなく、MySQLのバージョンやモードによって挙動が異なる可能性も示唆されています。安全のため、負の数の絶対値が文字列長を超える可能性のある場合は、IF文などで条件分岐させるか、LENGTH()関数を使って事前に位置を調整するなどの対策を検討する方が良いでしょう。

sql
-- 安全な方法の例 (LENGTHを使用)
SELECT
str,
pos,
len,
IF(ABS(pos) > LENGTH(str) AND pos < 0, -- 負の数で絶対値が文字列長より大きい場合
SUBSTR(str, 1, len), -- 先頭から抽出
SUBSTR(str, pos, len) -- 通常の抽出
) AS extracted_string
FROM (SELECT 'abc' AS str, -4 AS pos, 2 AS len) AS params;

この例は少し複雑ですが、負の数の開始位置が文字列長を超える場合の挙動をより明確に制御できます。ただし、多くの場合、負の数を使うのは末尾から数文字を取りたい場合など、絶対値が文字列長を超えない範囲での利用が一般的です。

2.3. lenの値の詳細

len引数は抽出する文字数を指定しますが、いくつか特殊なケースがあります。

• lenが省略された場合: posで指定された位置から文字列の最後までが抽出されます。これは非常に便利な構文です。

  sql
SELECT SUBSTR('Hello World', 7); -- 結果: 'World' (7文字目から最後まで)

• lenが残りの文字数より大きい場合: posで指定された位置から文字列の末尾までが抽出されます。指定した長さよりも短くなる可能性があります。

  sql
SELECT SUBSTR('Hello', 3, 10); -- 'Hello'の長さは5。3文字目('l')から残り3文字。指定長10は大きいが、末尾までとなる。
-- 結果: 'llo'

• lenが0の場合: 結果は常に空文字列''になります。

  sql
SELECT SUBSTR('Hello', 1, 0); -- 結果: ''

• lenが負の数の場合: 結果は常に空文字列''になります。これはエラーではなく、定義された挙動です。

  sql
SELECT SUBSTR('Hello', 1, -5); -- 結果: ''

2.4. エイリアス (SUBSTRING, MID)

先述の通り、SUBSTRINGおよびMIDはSUBSTR関数のエイリアスです。機能的には全く同じです。

sql
SELECT
SUBSTR('example', 2, 3) AS using_substr,
SUBSTRING('example', 2, 3) AS using_substring,
MID('example', 2, 3) AS using_mid;

結果：
+--------------+-----------------+-----------+
| using_substr | using_substring | using_mid |
+--------------+-----------------+-----------+
| xam | xam | xam |
+--------------+-----------------+-----------+

どれを使っても構いませんが、コードの可読性を高めるために、プロジェクト内で統一することをお勧めします。MySQLの公式ドキュメントではSUBSTRINGが最も一般的かもしれません。

2.5. 文字セットと照合順序 (Character Set and Collation)

MySQLの文字列関数は、使用されている文字セットと照合順序に影響を受けます。特にマルチバイト文字セット（UTF-8など）を使用している場合、1文字が複数バイトで構成されることがあります。

SUBSTR関数は、デフォルトでは文字数を基準として動作します。バイト数ではありません。例えば、UTF-8で日本語文字列を扱う場合、1文字が3バイトになることがありますが、SUBSTR('日本語', 1, 1)は正しく「日」の1文字を抽出します。

sql
SELECT SUBSTR('日本語', 1, 1); -- UTF-8環境の場合、結果: '日'

もしバイト単位で操作したい場合は、代わりにSUBSTRING_BYTES()関数を使用します。

sql
SELECT SUBSTRING_BYTES('日本語', 1, 3); -- UTF-8で「日」は3バイト
-- 結果: '日'

SUBSTRING_BYTES('日本語', 1, 4)のように指定すると、UTF-8では「日」の3バイトと「本」の1バイト目までが抽出され、文字化けする可能性があります。

通常、アプリケーションで扱う文字列は文字単位での操作が望ましいため、SUBSTRを使用すれば問題ありません。しかし、特定のバイナリデータや、バイトレベルでの厳密な処理が必要な場合は、SUBSTRING_BYTESの存在を知っておくと役立ちます。

3. SUBSTR関数を使った具体的な使用例

実際のデータベース操作におけるSUBSTR関数の様々な使用例を見ていきましょう。ここでは、架空のusersテーブルとproductsテーブルを想定します。

usersテーブル:
* user_id INT
* username VARCHAR(50)
* email VARCHAR(100)
* phone VARCHAR(20)
* signup_date DATE

productsテーブル:
* product_id INT
* product_name VARCHAR(100)
* description TEXT
* price DECIMAL(10, 2)
* sku VARCHAR(50)

例1：ユーザー名の最初の1文字を取り出す

ユーザー名の頭文字を使って、簡単な検索リストを作成したい場合など。

sql
SELECT username, SUBSTR(username, 1, 1) AS first_initial
FROM users;

例2：メールアドレスのドメイン部分を抽出する

@の位置を特定し、それ以降の部分を取り出します。LOCATE関数を使って@の位置を取得し、その位置をSUBSTRの開始位置として利用します。

sql
SELECT
email,
SUBSTR(email, LOCATE('@', email) + 1) AS email_domain
FROM users;

• LOCATE('@', email): @がemail文字列の中で最初に出現する位置を返します。
• LOCATE('@', email) + 1: その位置の次の文字（つまりドメインの開始位置）を指定します。
• SUBSTR(..., LOCATE('@', email) + 1): ドメインの開始位置から文字列の最後までを抽出します（lenを省略）。

もし@が含まれないメールアドレスがあった場合、LOCATEは0を返します。SUBSTR(..., 0 + 1)つまりSUBSTR(..., 1)となり、メールアドレス全体が抽出されます。もし@がない場合に空文字列を得たいなどの要件がある場合は、LOCATEの結果を確認する条件分岐を加える必要があるかもしれません。

例3：電話番号から市外局番を抽出する（固定フォーマット）

電話番号が「XXX-YYYY-ZZZZ」のような固定フォーマットで格納されていると仮定します。最初のハイフンまでが市外局番です。

sql
SELECT
phone,
SUBSTR(phone, 1, LOCATE('-', phone) - 1) AS area_code
FROM users;

• LOCATE('-', phone): 最初のハイフンの位置を取得します。
• LOCATE('-', phone) - 1: ハイフンの直前の位置（市外局番の末尾）を指定します。
• SUBSTR(..., 1, LOCATE('-', phone) - 1): 1文字目からハイフンの直前までの文字を抽出します。

ただし、電話番号のフォーマットが統一されていない場合（ハイフンがない、括弧があるなど）には、このクエリは正しく機能しません。より複雑なパターンには、正規表現関数（REGEXP_SUBSTRなど）が適している場合があります。

例4：長い商品説明の冒頭部分を抜き出してプレビューにする

Webサイトなどで、長い商品の説明をすべて表示するのではなく、冒頭の数十字だけを表示したい場合に使えます。

sql
SELECT
product_name,
SUBSTR(description, 1, 100) AS description_preview
FROM products;

これは単に先頭100文字を取り出すだけですが、実際には単語の途中で途切れないようにしたり、末尾に「…」を付け加えたりといった処理が必要になることが多いでしょう。これはSUBSTR単独では難しく、他の関数やアプリケーション側のロジックと組み合わせる必要があります。

例5：SKUコードから特定の部品番号を抽出する（固定長）

SKU（在庫管理単位）が「AAABBCDDDEFF」のように、各部分が固定長で意味を持つ場合に利用できます。例えば、5文字目から3文字だけ（CDDD部分）を抽出したい場合。

sql
SELECT sku, SUBSTR(sku, 5, 3) AS part_number
FROM products;

例6：文字列の末尾から数文字を抽出する（負のposを使用）

ファイルの拡張子を抽出したい場合など、文字列の末尾からの位置が分かっているが、文字列全体の長さは不定な場合に便利です。例えば、ファイルパスから拡張子を抽出したい場合。

sql
-- ファイルパスが '/path/to/document.pdf' のような形式
SELECT SUBSTR('/path/to/document.pdf', -3); -- 結果: 'pdf'
SELECT SUBSTR('/path/to/archive.tar.gz', -6); -- 結果: 'tar.gz'

この例では、ドットの位置を特定し、その次から最後までを抽出する方がより汎用的です。これもLOCATEと組み合わせることで実現できます。末尾から最初のドットの位置を探すには、少し工夫が必要です。LOCATEは先頭から探しますが、REVERSE()関数で文字列を反転させてからLOCATEを使い、その位置を元の文字列での位置に変換するというテクニックがあります。

“`sql
SELECT
file_path,
SUBSTR(file_path, LENGTH(file_path) – LOCATE(‘.’, REVERSE(file_path)) + 2) AS file_extension
FROM (SELECT ‘/path/to/document.pdf’ AS file_path) AS files;

— 解説:
— REVERSE(‘/path/to/document.pdf’) -> ‘fdp.tnemucod/ot/htap/’
— LOCATE(‘.’, REVERSE(file_path)) -> 反転文字列での’.’の位置 = 4 (左から4番目)
— LENGTH(file_path) -> 元文字列の長さ = 22
— 元文字列での’.’の位置 = LENGTH(file_path) – (反転文字列での’.’の位置) + 1
— = 22 – 4 + 1 = 19 (つまり、’d’の次)
— 拡張子の開始位置 = ‘.’の次の文字の位置
— = LENGTH(file_path) – LOCATE(‘.’, REVERSE(file_path)) + 1 + 1
— = 22 – 4 + 2 = 20 (つまり、’p’)
— SUBSTR(file_path, 20) -> 20文字目から最後まで
“`

この例のように、SUBSTRは他の文字列関数と組み合わせて使うことで、より複雑な文字列操作が可能になります。

4. SUBSTR関数と他の文字列関数との組み合わせ

前述の例でも見たように、SUBSTR単独では難しい処理も、他のMySQL文字列関数と組み合わせることで実現できます。ここでは、よくSUBSTRと一緒に使われる関数とその組み合わせ例を紹介します。

• LENGTH(str): 文字列strのバイト数を返します。
• CHAR_LENGTH(str) / CHARACTER_LENGTH(str): 文字列strの文字数を返します。マルチバイト文字セットでも正確な文字数を取得できます。通常、SUBSTRと組み合わせる場合はCHAR_LENGTHを使用します。
• LOCATE(substr, str) / INSTR(str, substr): substrがstrの中で最初に出現する位置を返します。見つからない場合は0を返します。LOCATEはオプションで検索開始位置を指定できますが、基本的な使い方は似ています。INSTRはLOCATEの引数の順序が逆になったエイリアスのようなものです。
• CONCAT(str1, str2, ...): 複数の文字列を結合します。
• LEFT(str, len): 文字列strの先頭からlen文字を抽出します。SUBSTR(str, 1, len)と同じです。
• RIGHT(str, len): 文字列strの末尾からlen文字を抽出します。SUBSTR(str, -len)と同じです。

これらの関数とSUBSTRを組み合わせることで、より高度な文字列処理が可能になります。

例1：文字列の長さを超えない範囲で安全にSUBSTRを使う

ユーザー入力など、長さが保証されない文字列から部分文字列を抽出する場合、指定した開始位置や長さが文字列の長さを超える可能性があります。CHAR_LENGTHを使って事前に長さを確認し、安全な範囲で抽出を行います。

“`sql
SELECT
my_string,
start_pos,
extract_len,
SUBSTR(my_string, start_pos, LEAST(extract_len, CHAR_LENGTH(my_string) – start_pos + 1)) AS safe_substring
FROM (SELECT ‘Hello World’ AS my_string, 7 AS start_pos, 10 AS extract_len) AS params;

— 解説:
— CHAR_LENGTH(my_string): 元文字列の文字数 = 11
— start_pos: 7
— 残りの文字数 = CHAR_LENGTH(my_string) – start_pos + 1 = 11 – 7 + 1 = 5
— LEAST(extract_len, 残りの文字数) = LEAST(10, 5) = 5
— 抽出する実際の長さは5となる。SUBSTR(‘Hello World’, 7, 5) -> ‘World’
“`

LEAST()関数を使うことで、指定した長さと、開始位置から末尾までの残りの文字数のうち、小さい方を実際の抽出長とすることができます。これにより、指定長が大きすぎても文字列の範囲を超えて抽出されることはありません。ただし、この方法はstart_posが1以上の正の数である場合に有効です。負の数や0の場合は追加の考慮が必要です。

より堅牢にするなら、posとlenが有効な範囲にあるかチェックするCASE文やIF文を使うのが最も安全です。

“`sql
SELECT
my_string,
pos,
len,
CASE
WHEN pos = 0 OR len < 0 THEN ” — pos=0 または lenが負の場合は空文字列
WHEN pos > 0 AND pos > CHAR_LENGTH(my_string) THEN ” — posが正で文字列長を超える場合は空文字列
WHEN pos < 0 AND ABS(pos) > CHAR_LENGTH(my_string) THEN ” — posが負で絶対値が文字列長を超える場合は空文字列
— その他の有効なケースで抽出
WHEN pos > 0 THEN SUBSTR(my_string, pos, LEAST(len, CHAR_LENGTH(my_string) – pos + 1))
WHEN pos < 0 THEN SUBSTR(my_string, pos, LEAST(len, ABS(pos))) — 負のposの場合、抽出可能な最大文字数はABS(pos)
ELSE ” — ここには来ないはずだが念のため
END AS safe_substring
FROM (SELECT ‘Hello World’ AS my_string, -5 AS pos, 10 AS len) AS params;

— 上記例の解説: my_string=’Hello World’, pos=-5, len=10
— posは負 (-5)
— ABS(pos) = 5 <= CHAR_LENGTH(‘Hello World’) = 11 なので、posは有効
— 抽出可能な最大文字数は ABS(pos) = 5
— LEAST(len, ABS(pos)) = LEAST(10, 5) = 5
— SUBSTR(‘Hello World’, -5, 5) を実行 -> ‘World’
``
※負のposの場合のlenの挙動について補足：MySQLのSUBSTRドキュメントによると、lenは開始位置から最大len文字を抽出しますが、文字列の末尾を超えないようにします。負のposの場合、末尾からの位置が開始点になりますが、そこからlen文字分抽出する際に文字列の先頭を超えて逆方向に抽出することはできません。なので、抽出可能な最大文字数は開始位置（末尾からのpos）から文字列の先頭までの文字数になります。上記のABS(pos)という計算は正確ではない可能性があります。正しくは、開始位置（末尾から数えた文字目）から先頭までの文字数です。
例えば、長さ7の文字列でpos=-3の場合、開始位置は'e'です。ここから先頭までは'e', 'd', 'c'の3文字です。len=10を指定しても3文字しか取れません。この3文字は元の文字列のcdeです。つまり、SUBSTR(‘abcdefg’, -3, 10)の結果は‘cde’ではなく、末尾から数えて3番目の文字（e）から10文字、ただし末尾を超えない、というルールが適用され、結果は‘efg’`になります。

私の前述の負のposの説明（特に「負の数の絶対値が文字列長を超える場合」や「抽出可能な最大文字数はABS(pos)」）に一部誤りがありました。MySQLのSUBSTR(str, pos, len)の挙動は以下のようです。

• pos > 0: pos文字目から開始。len文字抽出。文字列末尾を超える部分は含まない。
• pos < 0: abs(pos)文字を末尾から数えた位置から開始。len文字抽出。文字列末尾を超える部分は含まない。

例:SUBSTR('abcdefg', -3, 2) -> 長さ7。末尾から3文字目は’e’。ここから2文字 -> ‘ef’。
例:SUBSTR('abcdefg', -3, 5) -> 長さ7。末尾から3文字目は’e’。ここから5文字、末尾を超えない -> ‘efg’。
例:SUBSTR('abc', -4, 2) -> 長さ3。末尾から4文字目？ 無効な負のpos。MySQLのバージョンによっては空文字列を返す場合や、先頭から抽出する場合があるようです。公式ドキュメントは「If pos is negative, the starting position is pos characters from the end of the string.」と述べていますが、posの絶対値が文字列長を超える場合の厳密な定義はバージョンによって異なる可能性があるため、このケースは避けるのが賢明です。

安全な使い方としては、正のposを使う場合はLEAST(len, CHAR_LENGTH(str) - pos + 1)で長さを調整し、負のposを使いたい場合はpos_from_start = CHAR_LENGTH(str) + pos + 1のように計算して正のposに変換してから使うのが、挙動を予測しやすく安全でしょう。

“`sql
— 負のposを正のposに変換して使う例
SELECT
my_string,
pos, — 元の負のpos
len,
— posが負の場合、対応する1始まりの正の位置を計算。 posが正の場合はそのまま。
— pos=0の場合は無効なので0として扱う
calculated_pos,
SUBSTR(my_string, calculated_pos, LEAST(len, CHAR_LENGTH(my_string) – calculated_pos + 1)) AS safe_substring
FROM (
SELECT
‘Hello World’ AS my_string,
-5 AS pos, — 例: 末尾から5文字目
10 AS len,
CASE
WHEN pos = 0 THEN 0
WHEN pos > 0 THEN pos
WHEN pos < 0 THEN GREATEST(1, CHAR_LENGTH(‘Hello World’) + pos + 1) — 末尾からpos文字目の1始まり位置。1より小さくならないようにGREATEST
END AS calculated_pos
) AS params
WHERE calculated_pos > 0; — calculated_posが0の場合は結果なし（または別途処理）

— 上記例の解説: my_string=’Hello World’, pos=-5, len=10
— CHAR_LENGTH(‘Hello World’) = 11
— pos=-5 < 0 なので計算: GREATEST(1, 11 + (-5) + 1) = GREATEST(1, 7) = 7
— calculated_pos = 7 (つまり、末尾から5文字目は先頭から7文字目)
— SUBSTR(‘Hello World’, 7, LEAST(10, 11 – 7 + 1))
— SUBSTR(‘Hello World’, 7, LEAST(10, 5))
— SUBSTR(‘Hello World’, 7, 5) -> ‘World’
“`
この方法は少し複雑ですが、負のposの挙動を明示的に制御できるため、より安全性が高いと言えます。

例2：文字列の左側・右側を簡単に取り出す (LEFT, RIGHT)

LEFTとRIGHTは、SUBSTRの特定の使い方に対するシンタックスシュガー（より簡単な書き方）です。

“`sql
— LEFT(str, len) は SUBSTR(str, 1, len) と同じ
SELECT
LEFT(‘MySQL’, 3) AS using_left,
SUBSTR(‘MySQL’, 1, 3) AS using_substr;

— 結果: using_left = ‘MyS’, using_substr = ‘MyS’

— RIGHT(str, len) は SUBSTR(str, -len) と同じ
SELECT
RIGHT(‘MySQL’, 3) AS using_right,
SUBSTR(‘MySQL’, -3) AS using_substr;

— 結果: using_right = ‘SQL’, using_substr = ‘SQL’
“`

先頭や末尾からだけ抽出したい場合は、LEFTやRIGHTを使う方がコードが直感的で読みやすくなります。

例3：特定の区切り文字の前後の文字列を抽出 (LOCATE, SUBSTR)

これはメールアドレスの例で既に見ましたが、他の区切り文字でも同様に使えます。例えば、CSV形式の文字列から特定のフィールドを抽出したい場合（ただし、CSVのパースには限界があります）。

“`sql
— 文字列 ‘field1,field2,field3,field4′ から field2 を抽出
SELECT
str,
— 最初の’,’の位置を取得
pos1,
— 2番目の’,’の位置を取得 (pos1+1から検索開始)
pos2,
— pos1の次からpos2-pos1-1文字抽出
SUBSTR(str, pos1 + 1, pos2 – pos1 – 1) AS field2
FROM (
SELECT
‘field1,field2,field3,field4’ AS str,
LOCATE(‘,’, ‘field1,field2,field3,field4’) AS pos1
) AS t1, — 最初のLOCATEの結果を使うためサブクエリを使用
(
SELECT
LOCATE(‘,’, ‘field1,field2,field3,field4’, LOCATE(‘,’, ‘field1,field2,field3,field4’) + 1) AS pos2
) AS t2;

— 結果: field2 = ‘field2’
“`

このように、LOCATE関数で区切り文字の位置を複数回検索し、その位置情報を使ってSUBSTRで間の文字列を切り出すというパターンは非常に多いです。

例4：数値や日付を文字列として扱い、一部分を抽出する

MySQLは、数値型や日付型を文字列関数に渡すと、自動的に文字列に変換して処理します。

“`sql
— 数値から小数点以下を抽出
SELECT SUBSTR(123.456, LOCATE(‘.’, 123.456) + 1); — 結果: ‘456’

— 日付から年だけを抽出
SELECT SUBSTR(‘2023-10-27’, 1, 4); — 結果: ‘2023’ (固定フォーマットの場合)
— または YEAR() 関数の方が適切ですが、例として
SELECT SUBSTR(CURDATE(), 1, 4); — 現在の日付から年を抽出
“`

日付の場合は、YEAR(), MONTH(), DAY()などの専用関数を使う方が一般的で正確ですが、文字列として扱う必要がある特定のフォーマットの場合にはSUBSTRも有効です。

例5：文字列の長さに応じて抽出方法を変える

例えば、あるカラムの文字列が短い場合は全体を表示し、長い場合は冒頭部分に「…」を付けて表示する、といった処理もCHAR_LENGTHとSUBSTR、CONCATを組み合わせて実現できます。

sql
SELECT
description,
CHAR_LENGTH(description) AS desc_len,
CASE
WHEN CHAR_LENGTH(description) <= 50 THEN description
ELSE CONCAT(SUBSTR(description, 1, 50), '...')
END AS display_description
FROM products;

この例では、CASE文を使って文字列の長さを判定し、50文字以下ならそのまま表示、それより長ければSUBSTRで最初の50文字を抽出し、CONCATで「…」を付加しています。

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

SUBSTR関数を含む文字列操作関数をクエリで使用する際には、パフォーマンスへの影響を考慮することが重要です。

• インデックスの利用: 通常、カラムに対して文字列関数を適用すると、そのカラムに作成されたインデックスは利用されません。これは、関数を適用した後の「計算された値」に対して検索やフィルタリングを行うため、元のインデックスが直接参照できなくなるためです。例えば、WHERE SUBSTR(username, 1, 1) = 'A'のようなクエリは、usernameカラムにインデックスがあってもフルスキャンを引き起こす可能性が高いです。

  • 対策:
    • もし特定のパターン（例: 先頭がAの文字列）で頻繁に検索するなら、関数ベースのインデックス（MySQL 5.7以降で利用可能）や、生成カラム（Generated Columns, MySQL 5.7以降）を使用して、関数適用結果を格納し、そのカラムにインデックスを作成することを検討できます。
    • あるいは、アプリケーション側でデータを加工してから検索条件を構築する（例: 検索文字列自体にワイルドカードを使う WHERE username LIKE 'A%' はインデックスを利用できる可能性があります）。
    • 可能であれば、文字列関数を使わずにインデックスが利用できるクエリに書き換えることを検討します。

• 計算コスト: 文字列操作は、単純な数値比較などに比べて計算コストが高い場合があります。特に、大きな文字列に対して複雑な操作を多数のカラムや行にわたって実行する場合、クエリの実行時間が長くなる可能性があります。

  • 対策:
    • 必要な場面でのみ文字列関数を使用する。
    • 同じ計算を繰り返し行う場合は、計算結果を別のカラムに格納しておく（非正規化やキャッシュ）。
    • 複雑な文字列処理はアプリケーションレイヤーで行うことを検討する。
    • EXPLAINを使用してクエリの実行計画を確認し、パフォーマンスボトルネックとなっている箇所を特定する。

• 結果セットのサイズ: 部分文字列の抽出自体は結果セットのサイズを小さくする効果がありますが、抽出後の文字列をフィルタリングや集計に使用する場合、前述のインデックスの問題と組み合わさって性能に影響を与えることがあります。

総じて、SUBSTR関数自体が本質的に遅いわけではありませんが、それがクエリの中でどのように使われるかによって、パフォーマンスに大きな影響を与える可能性があります。特に大規模なデータセットを扱う場合は、これらの考慮事項を念頭に置いてクエリを設計することが重要です。

6. 関連する他の文字列関数との比較

SUBSTR関数と同様に部分文字列の抽出に関連する関数や、より複雑なパターンマッチングに使用される関数をいくつか紹介し、SUBSTRとの使い分けについて解説します。

• LEFT(str, len) / RIGHT(str, len):

  • 使い分け: 文字列の先頭または末尾からのみ一定の文字数を取り出したい場合に最適です。SUBSTRの特定のケース（SUBSTR(str, 1, len)とSUBSTR(str, -len)）のシンタックスシュガーであり、コードの意図がより明確になります。位置指定が先頭か末尾以外になる場合はSUBSTRを使用します。

• MID(str, pos, len):

  • 使い分け: SUBSTR(str, pos, len)のエイリアスです。機能は完全に同じです。どの関数名を使うかは、チームのコーディング規約や個人の好みに依存します。

• SUBSTRING_INDEX(str, delim, count):

  • 機能: 文字列strを区切り文字delimで分割し、指定したcount番目の部分文字列を返します。countが正の場合は左から、負の場合は右から数えます。
  • 使い分け: SUBSTRが「位置と長さ」で指定するのに対し、SUBSTRING_INDEXは「区切り文字」と「何番目の塊か」で指定します。CSV文字列やドメイン名など、区切り文字によって構造化されたデータから特定のフィールドを取り出したい場合に非常に便利です。区切り文字の位置をLOCATEで複数回取得し、その位置でSUBSTRを使うよりも、SUBSTRING_INDEXの方がシンプルに記述できることが多いです。

  sql
-- ドメイン名からトップレベルドメイン (com, orgなど) を抽出
SELECT SUBSTRING_INDEX('www.example.com', '.', -1); -- 結果: 'com'
-- サブドメイン部分 (www.example) を抽出
SELECT SUBSTRING_INDEX('www.example.com', '.', 2); -- 結果: 'www.example'

• 正規表現による抽出 (REGEXP_SUBSTR):

  • 機能: 正規表現パターンにマッチした部分文字列を抽出します。より複雑で柔軟なパターンマッチングが可能です。
  • 使い分け: SUBSTRは固定位置や単純な区切り文字による抽出には適していますが、特定の文字の繰り返しや、複数の候補パターン、文字クラス、量指定子など、複雑なパターンにマッチする部分を抽出したい場合は正規表現関数（REGEXP_SUBSTRなど）が強力です。ただし、正規表現は学習コストが高く、SUBSTRに比べてパフォーマンスも劣る傾向があるため、必要に応じて使い分けます。

  sql
-- 文字列内の最初の数字の並びを抽出 (例: 商品コードやバージョン番号)
SELECT REGEXP_SUBSTR('Item A12345-v2.0', '[0-9]+'); -- 結果: '12345'
REGEXP_SUBSTRはMySQL 8.0以降で利用可能です。それ以前のバージョンでは、正規表現でのマッチングはREGEXPやRLIKEで行えましたが、部分文字列の抽出は他の関数を組み合わせるか、プログラミング言語側で行う必要がありました。

SUBSTRは最も基本的な「位置と長さ」による部分文字列抽出関数です。多くの簡単なケースではこれで十分であり、学習コストも低いです。区切り文字による抽出にはSUBSTRING_INDEXが便利で、より複雑なパターンには正規表現を検討するというように、目的に応じて最適な関数を選択することが重要です。

7. まとめ：SUBSTR関数をマスターして文字列操作を自在に

この記事では、MySQLのSUBSTR関数について、その基本的な構文から詳細な挙動、多数の具体的な使用例、他の関数との組み合わせ、そしてパフォーマンスに関する考慮事項まで、幅広く解説しました。

SUBSTR関数は、指定した文字列から、1始まりのインデックスで指定した位置から、指定した長さだけ部分文字列を取り出すための基本的な関数です。負の数を開始位置として指定することで、文字列の末尾からの位置を指定することも可能です。SUBSTRINGやMIDは同等の機能を持つエイリアスです。

文字列操作はデータベース操作において非常に頻繁に登場するタスクです。SUBSTR関数をマスターすることは、データの整形、抽出、分析の幅を大きく広げます。メールアドレスからドメインを抽出したり、商品説明のプレビューを作成したり、固定フォーマットのデータから特定のフィールドを取り出したりと、様々な実務で役立つでしょう。

ただし、SUBSTR関数単独で解決できない複雑な要件には、LOCATE, CHAR_LENGTH, CONCAT, LEFT, RIGHT, SUBSTRING_INDEX, そして必要であれば正規表現関数などを組み合わせることで対応できます。また、文字列関数を多用するクエリはインデックスを利用しにくくなる傾向があるため、特に大規模なテーブルではパフォーマンスへの影響を考慮し、適切な設計や代替手段の検討が必要です。

MySQLの文字列操作関数は非常に豊富です。SUBSTRはその中でも最も基本となる「切り出し」の操作を担います。この記事を通じて、SUBSTR関数の挙動とその可能性を深く理解していただけたなら幸いです。

文字列操作のスキルは、データベースエンジニア、データアナリスト、アプリケーション開発者など、MySQLを扱う全ての人にとって非常に価値の高いものです。ぜひ、この記事で学んだ知識を活かして、実際のデータ操作でSUBSTR関数、そして他の関連関数を自在に使いこなしてください。

さらに学習を進める際は、以下の点に注目すると良いでしょう。

• MySQL公式ドキュメントの文字列関数に関する章を読む。
• SUBSTRING_INDEXや正規表現関数（REGEXP_SUBSTRなど）の詳しい使い方を学ぶ。
• 実際の業務データや練習用データを使って、様々な文字列操作を試す。
• 複雑な文字列整形やパースが必要な場合は、SQLだけで処理する限界を知り、アプリケーション側での処理も検討する。

文字列操作の基本であるSUBSTRをマスターし、データとの対話をより豊かにしていきましょう。

---

これで、MySQLのSUBSTR関数に関する詳細な記事は完了です。約5000語という目標に対して、可能な限り詳細な説明、多様な例、関連情報を含めて記述しました。しかし、AIの出力には制限があるため、厳密に5000語に達しているかは保証できません。内容はSUBSTR関数に焦点を当てつつ、関連するトピックにも触れています。