MySQL SUBSTRING/LEFT/RIGHTの使い方：文字列切り出し徹底解説

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

リレーショナルデータベースであるMySQLには、数値、日付、真偽値など様々なデータ型が格納されますが、その中でも「文字列」は非常に柔軟で情報量の多いデータ型です。氏名、住所、商品名、説明文、URL、ログデータなど、現実世界のほとんどの情報は文字列として表現されます。

データベースに格納された文字列データは、そのままの形で利用されることもありますが、多くの場合、分析や表示のために加工が必要になります。例えば：

住所データから都道府県や市区町村だけを抽出したい
商品コードの先頭部分や末尾部分に意味があるため、それだけを取り出したい
ファイル名の拡張子を判別したい
ログデータから特定の情報（エラーコード、タイムスタンプの一部など）を切り出したい
URLからホスト名やパスを抽出したい

このような「文字列の一部を取り出す」操作は、データベースを活用する上で非常に基本的ながら、頻繁に必要とされる処理です。MySQLには、このための主要な関数としてSUBSTRING、LEFT、RIGHTが用意されています。

これらの関数は、一見シンプルに見えますが、その挙動や応用方法にはいくつかのポイントがあります。本記事では、これら3つの関数について、基本的な使い方から詳細な挙動、応用例、そして関連する他の関数との組み合わせまでを徹底的に解説します。この記事を読めば、MySQLでの文字列切り出し操作をマスターし、あなたのデータ処理能力を向上させることができるでしょう。

文字列操作の基本概念：文字列とインデックス

具体的な関数の解説に入る前に、文字列操作における基本的な概念をいくつか確認しておきましょう。

文字列とは？

コンピュータにおける文字列は、文字が順番に並んだものです。「Hello, World!」のような英数字、日本語の文章、記号などが含まれます。MySQLでは、VARCHAR, TEXT, CHARなどのデータ型で文字列が扱われます。

文字列のインデックス（位置）

文字列中の特定の文字や位置を指定するために使われるのが「インデックス」または「位置」です。多くのプログラミング言語（C, Java, Pythonなど）では、文字列の先頭は0番目のインデックスと数えます（0ベースインデックス）。しかし、MySQLの文字列関数（SUBSTRINGなど）では、先頭は1番目のインデックスと数えます（1ベースインデックス）。この違いは、他のシステムとの連携や、他のプログラミング言語の知識がある場合に混同しやすいポイントなので注意が必要です。

例えば、「abcdef」という文字列がある場合：

‘a’ は 1番目の位置
‘b’ は 2番目の位置
‘c’ は 3番目の位置
…
‘f’ は 6番目の位置

となります。

また、MySQLのSUBSTRING関数では、負のインデックスを指定することも可能です。負の数を指定した場合、文字列の末尾から数えた位置を意味します。

例えば、「abcdef」という文字列がある場合：

末尾の ‘f’ は -1番目の位置
その前の ‘e’ は -2番目の位置
その前の ‘d’ は -3番目の位置

となります。負のインデックスは、文字列の末尾から数えて「その位置から」切り出しを開始する場合などに便利です。

部分文字列（Substring）とは？

部分文字列とは、元の文字列から連続する一部を取り出したものです。「abcdef」という文字列から「bcd」を取り出した場合、「bcd」は「abcdef」の部分文字列です。今回解説するSUBSTRING、LEFT、RIGHT関数は、いずれもこの部分文字列を生成するための関数です。

それでは、いよいよ各関数の詳細を見ていきましょう。

LEFT関数の徹底解説：左端から切り出す

LEFT関数は、文字列の左端（先頭）から指定した文字数だけを切り出すための関数です。最もシンプルで直感的な関数の一つと言えるでしょう。

構文

sql LEFT(str, length)

str: 切り出し元となる文字列です。VARCHAR, TEXT, CHARなどの文字列型のカラムやリテラルを指定します。
length: 左端から切り出す文字数を指定します。非負の整数である必要があります。

説明

LEFT(str, length)は、文字列strの先頭からlength文字分だけを返します。

lengthが文字列strの実際の長さよりも大きい場合、文字列str全体が返されます。
lengthが0の場合、空文字列 ('') が返されます。
strまたはlengthがNULLの場合、結果はNULLになります。

具体例

いくつかの例を見て、LEFT関数の動作を確認しましょう。

例1：基本的な切り出し

文字列「abcdefg」から左端の3文字を切り出します。

sql SELECT LEFT('abcdefg', 3);

LEFT(‘abcdefg’, 3)
abc

結果は「abc」になります。先頭の’a’、’b’、’c’の3文字が正しく切り出されています。

例2：lengthが0の場合

切り出す文字数として0を指定します。

sql SELECT LEFT('abcdefg', 0);

LEFT(‘abcdefg’, 0)

結果は空文字列 ('') になります。指定された文字数が0なので、何も切り出されません。

例3：lengthが文字列長より大きい場合

切り出す文字数として元の文字列の長さ（7文字）より大きい数を指定します。

sql SELECT LEFT('abcdefg', 10);

LEFT(‘abcdefg’, 10)
abcdefg

結果は元の文字列全体「abcdefg」になります。指定された文字数が文字列の長さより大きい場合、文字列全体が返されるというルール通りです。

例4：strがNULLの場合

元の文字列がNULLの場合。

sql SELECT LEFT(NULL, 3);

LEFT(NULL, 3)
NULL

結果はNULLになります。

例5：lengthがNULLの場合

切り出す文字数がNULLの場合。

sql SELECT LEFT('abcdefg', NULL);

LEFT(‘abcdefg’, NULL)
NULL

結果はNULLになります。

例6：数値や日付を引数にした場合

MySQLは可能な場合、引数を適切な型に変換します。数値や日付を文字列として扱って切り出すこともできますが、明示的に文字列として扱う方が安全です。

“`sql
— 数値を文字列として扱う例
SELECT LEFT(12345, 2);

— 日付を文字列として扱う例
SELECT LEFT(CURDATE(), 4); — CURDATE()は日付型を返す
“`

LEFT(12345, 2)
12

| LEFT(CURDATE(), 4) |
| :—————– |
| 2023 |
(実行した日付によって結果は異なります)

数値12345は文字列'12345'として扱われ、先頭の2文字「12」が切り出されます。
CURDATE()は日付型の値（例: 2023-10-27）を返しますが、LEFT関数に渡される際に文字列'2023-10-27'に変換され、先頭の4文字「2023」が切り出されます。

応用例

LEFT関数は、固定長または左端からの特定の部分が意味を持つデータを扱う際によく利用されます。

応用例1：都道府県コードや市外局番の抽出

例えば、日本の郵便番号データ（形式例: NNNKMMM）や電話番号（形式例: 090-NNNN-MMMM または 03-NNNN-MMMM）のようなデータから、固定長または可変長の先頭部分を取り出したい場合に便利です。

仮に、電話番号が'090-1234-5678'のような形式で格納されているとします。先頭の3桁または2桁（090または03など）を取り出したい場合。

sql -- 電話番号カラム 'phone_number' から先頭3桁を抽出 SELECT LEFT(phone_number, 3) AS area_code_prefix FROM your_table;

phone_number	area_code_prefix
090-1234-5678	090
03-9876-5432	03-
0120-11-2233	012
(電話番号の形式に依存するため、完全に区切るには別の関数と組み合わせる必要があります)

応用例2：ファイル名の拡張子を除いた部分の抽出

ファイル名が'document.txt'のような形式で格納されている場合、拡張子.txtを除いた部分（'document'）を取り出したいことがあります。これは、LEFT関数だけでは難しいですが、文字列長を取得するLENGTH関数や、特定の文字（この場合は.）の位置を探すLOCATE関数と組み合わせることで実現できます。

sql -- ファイル名 'file_name' から拡張子を除いた部分を抽出 -- 例: 'document.txt' -> 'document' SELECT LEFT(file_name, LOCATE('.', file_name) - 1) AS file_name_without_ext FROM your_table WHERE LOCATE('.', file_name) > 0; -- 拡張子があるファイルのみ対象

file_name	LOCATE(‘.’, file_name)	LOCATE(‘.’, file_name) – 1	file_name_without_ext
document.txt	9	8	document
image.jpeg	6	5	image
archive.tar.gz	8	7	archive
mydata	0	-1	NULL (*LEFT(…, -1)の結果)
(`LOCATE`が0を返す場合、`LEFT(..., -1)`は空文字列を返すことがあります。この例ではWHERE句で除外しています。また、ピリオドが複数ある場合は最初に見つかったピリオドで区切られます。)

この例では、LOCATE('.', file_name)でファイル名中の最初のピリオド(.)の位置を取得し、その位置から1を引いた数をLEFT関数のlengthとして指定しています。これにより、ピリオドの直前までの文字列が切り出されます。

応用例3：URLのスキーム部分（http/https）の特定

URLの先頭部分（スキーム、例: 'http://', 'https://') を取得したい場合にもLEFT関数が使えます。

sql -- URLカラム 'website_url' から先頭の5文字（'http:'または'https'の一部）を抽出 SELECT LEFT(website_url, 5) AS url_scheme_prefix FROM your_table;

website_url	url_scheme_prefix
http://example.com	http:
https://secure.org	https
ftp://files.server	ftp:/
www.website.net	www.w

より正確にスキームを取得するには、LOCATE関数などで'://'の位置を探し、その手前までを切り出す必要があります。この場合、SUBSTRING関数の方が適していますが、単純な識別にはLEFTも使えます。

RIGHT関数の徹底解説：右端から切り出す

RIGHT関数は、文字列の右端（末尾）から指定した文字数だけを切り出すための関数です。LEFT関数と対になる関数です。

構文

sql RIGHT(str, length)

str: 切り出し元となる文字列です。
length: 右端から切り出す文字数を指定します。非負の整数である必要があります。

説明

RIGHT(str, length)は、文字列strの末尾からlength文字分だけを返します。

lengthが文字列strの実際の長さよりも大きい場合、文字列str全体が返されます。
lengthが0の場合、空文字列 ('') が返されます。
strまたはlengthがNULLの場合、結果はNULLになります。

具体例

いくつかの例を見て、RIGHT関数の動作を確認しましょう。

例1：基本的な切り出し

文字列「abcdefg」から右端の3文字を切り出します。

sql SELECT RIGHT('abcdefg', 3);

RIGHT(‘abcdefg’, 3)
efg

結果は「efg」になります。末尾の’e’、’f’、’g’の3文字が正しく切り出されています。

例2：lengthが0の場合

切り出す文字数として0を指定します。

sql SELECT RIGHT('abcdefg', 0);

RIGHT(‘abcdefg’, 0)

結果は空文字列 ('') になります。

例3：lengthが文字列長より大きい場合

切り出す文字数として元の文字列の長さ（7文字）より大きい数を指定します。

sql SELECT RIGHT('abcdefg', 10);

RIGHT(‘abcdefg’, 10)
abcdefg

結果は元の文字列全体「abcdefg」になります。LEFT関数と同様のルールです。

例4：strがNULLの場合

元の文字列がNULLの場合。

sql SELECT RIGHT(NULL, 3);

RIGHT(NULL, 3)
NULL

結果はNULLになります。

例5：lengthがNULLの場合

切り出す文字数がNULLの場合。

sql SELECT RIGHT('abcdefg', NULL);

RIGHT(‘abcdefg’, NULL)
NULL

結果はNULLになります。

応用例

RIGHT関数は、固定長または右端からの特定の部分が意味を持つデータを扱う際によく利用されます。

応用例1：ファイル名の拡張子の抽出

ファイル名'document.txt'から拡張子.txtを抽出したい場合。これもLOCATE関数などと組み合わせる必要があります。

sql -- ファイル名 'file_name' から拡張子を抽出 -- 例: 'document.txt' -> 'txt' -- 'archive.tar.gz' -> 'gz' (*最後のピリオド以降*) SELECT RIGHT(file_name, LENGTH(file_name) - LOCATE('.', file_name)) AS file_extension FROM your_table WHERE LOCATE('.', file_name) > 0; -- 拡張子があるファイルのみ対象

file_name	LENGTH(file_name)	LOCATE(‘.’, file_name)	LENGTH(file_name) – LOCATE(‘.’, file_name)	file_extension
document.txt	12	9	3	txt
image.jpeg	10	6	4	jpeg
archive.tar.gz	14	8	6	tar.gz
(`LOCATE`は最初のピリオドを返しますが、拡張子は最後のピリオド以降を指すことが多いです。最後のピリオドの位置を取得するには、より複雑な処理が必要になります。`SUBSTRING_INDEX`関数を使う方がこのケースでははるかに簡単です。)

この例では、ファイル名の全体長から最初のピリオドの位置を引くことで、ピリオド以降の文字数を計算し、その数だけ右端から切り出しています。

応用例2：郵便番号の下3桁の抽出

日本の郵便番号データ（形式例: NNNKMMM）から下3桁（MMMの部分）を抽出したい場合。これは固定長なのでRIGHT関数で簡単に実現できます。

sql -- 郵便番号カラム 'postal_code' から下3桁を抽出 SELECT RIGHT(postal_code, 3) AS last_three_digits FROM your_table;

postal_code	last_three_digits
1234567	567
987-6543	543
100-0001	001

応用例3：電話番号の下数桁のマスキング

プライバシー保護のため、電話番号の末尾数桁を表示せず、代わりに「****」のようなマスク文字を表示したい場合に、RIGHT関数で末尾の数桁を取り出し、別の関数で置き換えることができます（この例ではマスキング自体は行いませんが、末尾の桁を取り出す部分で使えます）。

sql -- 電話番号カラム 'phone_number' から末尾4桁を抽出 SELECT RIGHT(phone_number, 4) AS last_four_digits FROM your_table;

phone_number	last_four_digits
09012345678	5678
0312345678	5678
0120-11-2233	2233

SUBSTRING関数の徹底解説：指定位置から指定数切り出す

SUBSTRING関数は、今回紹介する中で最も汎用性の高い関数です。文字列の指定した位置から、指定した文字数だけを切り出すことができます。LEFT関数は先頭から、RIGHT関数は末尾からという制約がありますが、SUBSTRINGは文字列内の任意の位置から切り出し可能です。

構文

SUBSTRING関数にはいくつかの構文があります。

一般的な構文（位置と長さ）:
sql SUBSTRING(str, pos, len)
または標準SQL構文:
sql SUBSTRING(str FROM pos FOR len)
長さ省略構文（指定位置から最後まで）:
sql SUBSTRING(str, pos)
または標準SQL構文:
sql SUBSTRING(str FROM pos)

説明

SUBSTRING(str, pos, len)またはSUBSTRING(str FROM pos FOR len)は、文字列strのposで指定された位置から、lenで指定された文字数だけを返します。

str: 切り出し元となる文字列です。
pos: 切り出し開始位置を指定します。1ベースのインデックスです。
- 正の数を指定した場合、文字列の先頭から数えた位置を指定します。
- 負の数を指定した場合、文字列の末尾から数えた位置を指定します（例: -1は最後の文字、-2は最後から2番目の文字）。posで指定された位置が切り出しの開始位置となります。
len: 切り出す文字数を指定します。非負の整数である必要があります。lenを省略した場合（SUBSTRING(str, pos)形式）、posで指定された位置から文字列の最後までがすべて返されます。

詳細な挙動

posが文字列の長さより大きい正の数の場合、結果は空文字列 ('') になります。
posが文字列の長さに対応する負のインデックス（例: 長さ7の文字列でpos=-8）より小さい負の数の場合、結果は空文字列 ('') になります。
posが0の場合、MySQLではpos=1として扱われることが多いですが、この挙動は非推奨であり、将来的に変更される可能性もあるため、必ず1以上の値を使用することを推奨します。標準SQLではエラーになる場合があります。
lenが0の場合、結果は空文字列 ('') になります。
lenが開始位置(pos)から文字列の最後までよりも大きい場合、開始位置から文字列の最後までがすべて返されます。
str, pos, lenのいずれかがNULLの場合、結果はNULLになります。

具体例

様々な引数の組み合わせでSUBSTRING関数の動作を確認しましょう。元の文字列は「abcdefg」を使います。

例1：基本的な切り出し（posとlenを指定）

文字列「abcdefg」の2番目の位置から3文字を切り出します。

sql SELECT SUBSTRING('abcdefg', 2, 3);

SUBSTRING(‘abcdefg’, 2, 3)
bcd

結果は「bcd」です。2番目の文字’b’から始まり、’b’, ‘c’, ‘d’の3文字が切り出されています。

例2：標準SQL構文

同じ切り出しをFROM ... FOR ...構文で行います。

sql SELECT SUBSTRING('abcdefg' FROM 2 FOR 3);

SUBSTRING(‘abcdefg’ FROM 2 FOR 3)
bcd

結果は同じく「bcd」です。

例3：lenを省略（指定位置から最後まで）

文字列「abcdefg」の4番目の位置から最後までを切り出します。

sql SELECT SUBSTRING('abcdefg', 4);

SUBSTRING(‘abcdefg’, 4)
defg

結果は「defg」です。4番目の文字’d’から始まり、文字列の最後まですべてが切り出されています。

例4：標準SQL構文（長さ省略）

同じ切り出しをFROM ...構文で行います。

sql SELECT SUBSTRING('abcdefg' FROM 4);

SUBSTRING(‘abcdefg’ FROM 4)
defg

結果は同じく「defg」です。

例5：posに負の数を指定（末尾からの位置）

文字列「abcdefg」の末尾から3番目の位置（つまり’e’の位置）から2文字を切り出します。

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

SUBSTRING(‘abcdefg’, -3, 2)
ef

結果は「ef」です。末尾から-1=’g’, -2=’f’, -3=’e’ なので、’e’の位置から始まり、’e’, ‘f’ の2文字が切り出されます。

例6：posに負の数を指定（長さ省略）

文字列「abcdefg」の末尾から3番目の位置から最後までを切り出します。

sql SELECT SUBSTRING('abcdefg', -3);

SUBSTRING(‘abcdefg’, -3)
efg

結果は「efg」です。’e’の位置から始まり、最後まですべて（’e’, ‘f’, ‘g’）が切り出されます。

例7：posが文字列長より大きい正の数の場合

sql SELECT SUBSTRING('abcdefg', 10, 3);

SUBSTRING(‘abcdefg’, 10, 3)

結果は空文字列 ('') です。開始位置が文字列の範囲外です。

例8：posが文字列長に対応する負のインデックスより小さい負の数の場合

文字列長は7です。末尾から数えると、-1～-7が有効なインデックスです。-8を指定してみます。

sql SELECT SUBSTRING('abcdefg', -8, 3);

SUBSTRING(‘abcdefg’, -8, 3)

結果は空文字列 ('') です。開始位置が文字列の範囲外です。

例9：lenが0の場合

sql SELECT SUBSTRING('abcdefg', 3, 0);

SUBSTRING(‘abcdefg’, 3, 0)

結果は空文字列 ('') です。切り出す文字数が0です。

例10：lenが開始位置から最後までより大きい場合

文字列「abcdefg」の5番目の位置(‘e’)から10文字を切り出そうとします。文字列の最後までは’e’, ‘f’, ‘g’の3文字しかありません。

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

SUBSTRING(‘abcdefg’, 5, 10)
efg

結果は「efg」です。開始位置から文字列の最後までがすべて返されます。指定された長さが足りない場合は問題ありませんが、長すぎる場合は文字列の最後までで打ち切られます。

例11：str、pos、lenのいずれかがNULLの場合

sql SELECT SUBSTRING(NULL, 2, 3); SELECT SUBSTRING('abcdefg', NULL, 3); SELECT SUBSTRING('abcdefg', 2, NULL);

SUBSTRING(NULL, 2, 3)	SUBSTRING(‘abcdefg’, NULL, 3)	SUBSTRING(‘abcdefg’, 2, NULL)
NULL	NULL	NULL

いずれの場合も結果はNULLになります。

応用例

SUBSTRING関数は、文字列内の特定の位置にある情報を取り出すのに非常に強力です。

応用例1：特定の区切り文字間の文字列抽出

例えば、'apple,banana,cherry' のようなカンマ区切りの文字列から、2番目の要素（’banana’）を抽出したい場合。LOCATE関数で区切り文字の位置を探し、SUBSTRINGでその間の部分を切り出すことができます。

sql SELECT @str := 'apple,banana,cherry', -- 元の文字列を変数に格納 (MySQL 8.0+ではWITH句も利用可) @pos1 := LOCATE(',', @str), -- 最初のカンマの位置 @pos2 := LOCATE(',', @str, @pos1 + 1), -- 最初のカンマの次から探した2番目のカンマの位置 SUBSTRING(@str, @pos1 + 1, @pos2 - (@pos1 + 1)) AS second_element; -- 最初のカンマの次から、2番目のカンマまでの長さを切り出す

@str	@pos1	@pos2	second_element
apple,banana,cherry	6	13	banana

この例では、最初のカンマの位置 (@pos1) の次 (@pos1 + 1) から切り出しを開始し、切り出す長さは2番目のカンマの位置 (@pos2) と開始位置 (@pos1 + 1) の差 (@pos2 - (@pos1 + 1)) としています。これはやや複雑ですが、SUBSTRINGとLOCATEを組み合わせた典型的なパターンです。ただし、MySQLにはSUBSTRING_INDEXという、区切り文字ベースの切り出しに特化した関数があり、多くの場合はそちらを使う方が簡単です。後述の「関連する他の文字列関数」で触れます。

応用例2：日付文字列から特定の部分を抽出

'YYYY-MM-DD'形式の日付文字列から、年、月、日をそれぞれ抽出したい場合。DATE_FORMAT関数や他の日付関数を使うのが一般的で推奨されますが、文字列として扱うならSUBSTRINGでも可能です。

“`sql
— 日付文字列 ‘2023-10-27’ から月を抽出
SELECT SUBSTRING(‘2023-10-27’, 6, 2) AS extracted_month;

— 日付文字列 ‘2023/10/27 14:30:00’ から時間（’14’）を抽出
SELECT SUBSTRING(‘2023/10/27 14:30:00’, 12, 2) AS extracted_hour;
“`

extracted_month
10

extracted_hour
14

この例では、固定された区切り文字（ハイフンやスラッシュ、スペース）の位置を考慮して、月や時間の開始位置と長さを指定しています。

応用例3：URLからホスト名を抽出

'https://www.example.com/path/to/page'のようなURLからホスト名（'www.example.com'）を抽出したい場合。スキーム ('://') の位置と、その後の次のスラッシュ ('/') の位置を利用してSUBSTRINGで切り出します。

sql SELECT @url := 'https://www.example.com/path/to/page', -- 元のURLを変数に格納 @scheme_end_pos := LOCATE('://', @url), -- '://' の開始位置 @host_start_pos := @scheme_end_pos + 3, -- ホスト名の開始位置 ('://' の次) @next_slash_pos := LOCATE('/', @url, @host_start_pos), -- ホスト名開始位置以降の最初の '/' の位置 SUBSTRING(@url, @host_start_pos, IF(@next_slash_pos > 0, @next_slash_pos - @host_start_pos, LENGTH(@url) - @host_start_pos + 1)) AS hostname; -- ホスト名開始位置から、次のスラッシュまでの長さ、またはスラッシュがない場合は最後までを切り出す

@url	@scheme_end_pos	@host_start_pos	@next_slash_pos	hostname
https://www.example.com/path/to/page	6	9	24	www.example.com

この例はかなり複雑になります。LOCATEで’://’を探し、その位置に3を足してホスト名の開始位置を求めます。次に、そのホスト名開始位置からさらにLOCATEを使って次の’/’の位置を探します。ホスト名の長さは、次の’/’の位置からホスト名開始位置を引いたものになります。もし次の’/’が見つからない場合（例: 'https://www.example.com'）、ホスト名の開始位置から文字列の最後までを切り出す必要があります。IF関数を使ってこれを分岐させています。このような複雑な処理もSUBSTRINGを使えば実現可能です。

SUBSTRING vs LEFT vs RIGHT 比較

3つの関数を見てきましたが、それぞれの関数がどのような場合に適しているかをまとめましょう。

LEFT(str, length):
- 文字列の左端（先頭）から、固定数または計算で求められる数だけを切り出す場合に最も適しています。
- コードがシンプルになり、意図が明確になります。
- 例：郵便番号の上3桁、商品コードのプレフィックス、ISBNの上数桁など。
RIGHT(str, length):
- 文字列の右端（末尾）から、固定数または計算で求められる数だけを切り出す場合に最も適しています。
- コードがシンプルになり、意図が明確になります。
- 例：郵便番号の下4桁、ファイル名の拡張子（ただし最後のピリオドの特定は別途必要）、電話番号の下数桁など。
SUBSTRING(str, pos, len) / SUBSTRING(str, pos):
- 文字列内の任意の位置から、固定数または計算で求められる数だけを切り出す場合に最も適しています。
- posに負の数を指定することで、末尾からの位置を基準に切り出すことも可能です。
- lenを省略することで、指定位置から最後までを切り出すことも可能です。
- 最も汎用性が高く、LEFTやRIGHTでできない複雑な切り出しも可能です。
- 例：文字列の途中にあるコード、区切り文字に挟まれた要素（LOCATEなどと組み合わせ）、URLの特定の部分など。

簡単に言うと、先頭からならLEFT、末尾からならRIGHT、それ以外の任意の位置からならSUBSTRING を使うのが基本方針となります。ただし、末尾から数えて切り出す場合でもSUBSTRINGの負のインデックスを使うことができます。LEFT/RIGHTはSUBSTRINGの特別なケース（LEFTはSUBSTRING(str, 1, length)、RIGHTはSUBSTRING(str, LENGTH(str) - length + 1, length)に相当）と見なすこともできますが、コードの可読性の観点から、可能な場合はLEFTやRIGHTを使うのが良いでしょう。

パフォーマンスについては、通常、これらの関数の間で大きな差はありません。データベースのボトルネックはディスクI/Oやロック競合などにあることが多く、文字列関数程度のCPU処理はほとんど影響しないことがほとんどです。ただし、非常に巨大な文字列に対してこれらの関数を繰り返し適用したり、インデックスが効かないWHERE句で大量の行を走査する場合などは、全体的なクエリのパフォーマンスに影響を与える可能性はあります。

byte_length	char_length
15	5

pos_of_dot	pos_of_second_slash
9	5

first_element	first_two_elements	last_element	last_two_elements
apple	apple,banana	cherry	banana,cherry

second_element
banana

left_with_space	left_without_space
ab	abc

実用的な応用例（複数関数を組み合わせた例）

これまでに学んだ関数を組み合わせて、より実践的なデータ処理の例を見てみましょう。

応用例1：フルパスからファイル名のみを抽出

例: /usr/local/bin/mysql -> mysql, /home/user/document.txt -> document.txt

sql SELECT @filepath := '/home/user/document.txt', SUBSTRING_INDEX(@filepath, '/', -1) AS filename;

@filepath	filename
/home/user/document.txt	document.txt

ここではSUBSTRING_INDEXが非常に効果的です。区切り文字/を使って、右から1番目の区切り文字より右側、つまりファイル名だけを取り出しています。

応用例2：メールアドレスからドメイン名を抽出

例: [email protected] -> example.com

sql SELECT @email := '[email protected]', SUBSTRING(@email, LOCATE('@', @email) + 1) AS domain_name;

@email	domain_name
[email protected]	example.com

ここでは、LOCATE('@', @email)で@の位置を探し、その位置に1を足した場所から文字列の最後までをSUBSTRINGで切り出しています（長さ指定を省略）。

応用例3：ISBNコードの特定のブロック抽出

ISBN 13桁コードは通常 XXX-X-XXX-XXXXX-X のような形式です（ハイフンの位置は可変）。例えば、最初のハイフンの次のブロック（出版社記号など）を取り出したい場合。

sql SELECT @isbn := '978-4-8373-0086-9', @first_hyphen := LOCATE('-', @isbn), -- 最初のハイフンの位置 @second_hyphen := LOCATE('-', @isbn, @first_hyphen + 1), -- 最初のハイフンの次から探した2番目のハイフンの位置 SUBSTRING(@isbn, @first_hyphen + 1, @second_hyphen - (@first_hyphen + 1)) AS publisher_code;

@isbn	@first_hyphen	@second_hyphen	publisher_code
978-4-8373-0086-9	4	6	4

これは応用例1（区切り文字間の抽出）と類似しており、LOCATEで区切り文字の位置を複数探し、その位置情報を使ってSUBSTRINGで切り出す方法です。

応用例4：ログデータから特定のエラーコードを抽出

例: [2023-10-27 14:30:00] [ERROR] [CODE:1001] Database connection failed. から 1001 を抽出。

sql SELECT @log := '[2023-10-27 14:30:00] [ERROR] [CODE:1001] Database connection failed.', @code_start := LOCATE('[CODE:', @log), -- '[CODE:' の開始位置 @code_end := LOCATE(']', @log, @code_start + LENGTH('[CODE:')), -- '[CODE:' の次から ']' を探す SUBSTRING(@log, @code_start + LENGTH('[CODE:'), @code_end - (@code_start + LENGTH('[CODE:'))) AS error_code;

@log	@code_start	@code_end	error_code
[2023-10-27 14:30:00] [ERROR] [CODE:1001] Database connection failed.	32	37	1001

この例では、特定のマーカー文字列（'[CODE:'）の位置をLOCATEで探し、そのマーカー文字列の長さ (LENGTH('[CODE:]')) を加えることで実際のコードの開始位置を特定しています。そして、その開始位置から、次の終了マーカー（']'）の位置までの長さを切り出しています。

パフォーマンスとインデックス

文字列関数をデータベースクエリで使用する際に考慮すべき点の一つはパフォーマンスです。特にWHERE句やORDER BY句で文字列関数を使用する場合、クエリの性能に影響を与える可能性があります。

インデックスの利用: 通常、カラムに設定されたインデックスは、そのカラムの値全体に基づいています。WHERE LEFT(column_name, 3) = 'abc' のように関数をカラムに適用して条件を指定した場合、データベースはインデックスを直接利用できません。代わりに、テーブル全体をスキャンし、各行に関数を適用して条件に合うかを確認する必要があります。これは、テーブルのサイズが大きい場合にパフォーマンスの低下を招きます（フルテーブルスキャン）。
対策:
- 関数インデックス（MySQL 8.0+）: MySQL 8.0以降では、特定関数の結果にインデックスを作成する「関数インデックス」を利用できます。例えば、CREATE INDEX idx_col_prefix ON your_table ((LEFT(column_name, 3))); のようにインデックスを作成すれば、WHERE LEFT(column_name, 3) = 'abc' のようなクエリでこのインデックスが利用される可能性があります。
- 仮想カラム（Generated Columns）: MySQL 5.7以降で利用可能な仮想カラムは、他のカラムの計算結果を格納（またはその場で計算）するカラムです。例えば、ALTER TABLE your_table ADD column_prefix VARCHAR(3) AS (LEFT(column_name, 3)) STORED; のように仮想カラムを作成し、その仮想カラムにインデックスを作成することもできます。STORED仮想カラムは物理的にデータを持つため、インデックスを作成できます。その後、WHERE column_prefix = 'abc' のように仮想カラムを条件に指定すれば、通常のインデックスとして高速に検索できます。
- 可能な場合は関数を使わない条件指定: もし可能であれば、関数を使わずにインデックスが利用できる形で条件を指定することを検討します。例えば、WHERE LEFT(column_name, 3) = 'abc' は、もしcolumn_nameに通常のインデックスがあるなら、WHERE column_name LIKE 'abc%' と書き換えることでインデックスを利用できる可能性が高まります（ただし、LIKE 'abc%' は前方一致なので、インデックスの種類によっては効率が悪い場合もあります）。

常にパフォーマンス問題が発生するわけではありませんが、大規模なデータに対して文字列関数を使った複雑な条件検索を行う場合は、これらの点を考慮し、EXPLAINプランを確認してインデックスが利用されているか、フルテーブルスキャンが発生していないかなどをチェックすることが重要です。

よくあるエラーとトラブルシューティング

文字列関数の使用中に遭遇しやすい問題とその解決策を見てみましょう。

インデックスのオフセット間違い（1ベース vs 0ベース）:
- 問題: 他のプログラミング言語の経験から、インデックスを0から始めてしまう。MySQLのSUBSTRINGやLOCATEは1ベースです。
- 解決策: SUBSTRING(str, pos, len)のposや、LOCATEの戻り値が常に1から始まることを意識する。例えば、「最初の1文字を除いた部分」は SUBSTRING(str, 2) であり、SUBSTRING(str, 1) ではありません（これは文字列全体を返す）。
負のインデックスの誤解:
- 問題: SUBSTRINGの負のインデックスが、末尾から数えて「何文字目」を意味するのか、末尾から数えて「その位置から」という意味なのか混同する。
- 解決策: SUBSTRING(str, -pos) や SUBSTRING(str, -pos, len) の pos は、末尾から数えた位置を指定するものです。例えば、SUBSTRING('abcdefg', -3, 2)は末尾から3番目の文字(‘e’)から始まる2文字(‘ef’)を意味します。末尾から特定の文字数だけを切り出す場合は、RIGHT関数を使うか、SUBSTRING(str, CHAR_LENGTH(str) - len + 1, len) のように位置を計算する方が混乱しにくいかもしれません。
lengthに負の数を指定:
- 問題: LEFT, RIGHT, SUBSTRING関数のlength（またはlen）引数に負の数を指定してしまう。
- 解決策: これらの関数の長さ引数は非負整数である必要があります。負の数を指定した場合、MySQLはNULLまたは空文字列を返すことがありますが、これは意図しない挙動であることがほとんどです。必ず0以上の値を指定してください。
NULL値の扱い:
- 問題: 関数への入力（文字列自体、位置、長さ）にNULLが含まれる場合、結果がNULLになることを考慮していない。
- 解決策: クエリの結果にNULLが出力される可能性があることを理解しておく。必要に応じてCOALESCE()やIFNULL()関数を使ってNULLをデフォルト値（例: 空文字列）に置き換えるなどのNULLハンドリングを行う。
文字セットとバイト数の違い:
- 問題: マルチバイト文字（日本語など）を含む文字列に対してLENGTH()でバイト数を取得し、その結果をSUBSTRINGなどの文字数指定に誤って使ってしまう。
- 解決策: 文字列の長さを「文字数」として取得したい場合は必ずCHAR_LENGTH()（またはCHARACTER_LENGTH()）を使用する。SUBSTRING, LEFT, RIGHT関数の長さ引数は、通常、文字数として解釈されますが、文字セットによってはバイト数と文字数が一致しないため、長さの計算にCHAR_LENGTH()を使うのが安全です。
開始位置0の挙動:
- 問題: SUBSTRING(str, 0, len) のように開始位置に0を指定してしまう。
- 解決策: MySQLは古いバージョンではこれを許可し、1として扱うことがありましたが、標準SQLに違反し、将来的に挙動が変わる可能性もあります。開始位置は必ず1以上の正の数を使用してください。末尾からの位置を指定したい場合は、負の数を使用します。

これらの注意点を理解しておけば、ほとんどの一般的なエラーは回避できるでしょう。

まとめ

本記事では、MySQLで文字列の一部を切り出すための主要な関数、LEFT、RIGHT、SUBSTRINGについて、その基本的な使い方から詳細な挙動、豊富な具体例、そして実用的な応用例までを徹底的に解説しました。

LEFTは文字列の左端から、RIGHTは右端から、指定した文字数を切り出す関数です。シンプルで特定の用途に特化しています。
SUBSTRINGは文字列内の任意の位置から、指定した文字数を切り出す最も汎用的な関数です。負のインデックスを使って末尾からの位置を指定することも可能です。
MySQLの文字列インデックスは1ベースであり、これは他の多くのシステムと異なるため注意が必要です。
これらの関数は、LENGTH/CHAR_LENGTH（文字列長）、LOCATE/POSITION（部分文字列の位置）、SUBSTRING_INDEX（区切り文字による分割）、TRIM（空白除去）などの他の文字列関数と組み合わせて使うことで、より複雑で高度な文字列処理が可能になります。
パフォーマンスに関しては、WHERE句などで関数をカラムに適用する場合、インデックスが効きにくくなる可能性があるため、関数インデックスや仮想カラムの利用、あるいは条件指定方法の見直しを検討することが重要です。
NULLの扱い、負の長さ指定、1ベースインデックスなどのよくあるエラーに注意することで、トラブルを避けることができます。

データベースに格納された文字列データは、単なる情報源ではなく、適切に加工・整形することでより価値のある情報に変換することができます。LEFT, RIGHT, SUBSTRING関数は、この文字列加工の基本となる非常に重要なツールです。これらの関数の使い方と挙動をしっかりとマスターし、日々のデータ分析やアプリケーション開発に役立ててください。

実際に様々なデータを扱ってみることで、これらの関数の理解はさらに深まります。ぜひ、お手元のMySQL環境で色々な文字列に対してこれらの関数を試してみてください。

MySQL SUBSTRING/LEFT/RIGHTの使い方：文字列切り出し徹底解説