技術資料

SELECTで文字列を取得するとき、「両端の空白を取りたい」「ハイフンで区切られたコードの一部だけ欲しい」「文章の中にあるコードを取り出したい」といったことがあります。

今回は、そんなときに使えるMySQLのTRIM、SUBSTRING_INDEX、REGEXP_SUBSTRを、基本の使い方から整理します。SELECTの基本が分かる人向けの内容です。

説明対象はMySQL 8.4です。仕様は2026年10月11日時点の公式資料で確認しています。MariaDBなど、別のDBへそのまま一般化しないでください。

整形したいのか、抽出したいのかで選ぶ

関数	主な用途
TRIM	文字列の先頭・末尾から、空白や指定した文字列を取り除く。
SUBSTRING_INDEX	区切り文字の位置を基準に、左側や右側の部分を取り出す。
REGEXP_SUBSTR	正規表現のパターンに一致する部分を取り出す。

例えば、両端の空白を除くだけならTRIMで済みます。区切り方が決まっているコードならSUBSTRING_INDEX、文章のどこにあるか分からないコードを形式で探すならREGEXP_SUBSTR、というように考えると選びやすいかと思います。

日本語を含む例は、UTF-8のSQLを送受信できるクライアントを使い、接続の文字セットはutf8mb4を前提にします。実際に試すときは、まず接続先のバージョンと文字セットを確認しておきます。

SELECT
    VERSION() AS server_version,
    @@character_set_client AS client_charset,
    @@character_set_connection AS connection_charset,
    @@character_set_results AS results_charset,
    @@collation_connection AS connection_collation,
    @@sql_mode AS sql_mode;

ここで分かるのは、接続しているDBサーバーの情報です。クライアント自体のバージョンとは区別します。文字セットの詳細は、MySQL公式の接続文字セットの説明を参照してください。

TRIMで先頭・末尾の文字を取り除く

TRIMは、次のように使います。ここでは、構文の各部分を日本語で示しています。

TRIM(対象文字列)
TRIM(LEADING 除去する文字列 FROM 対象文字列)
TRIM(TRAILING 除去する文字列 FROM 対象文字列)
TRIM(BOTH 除去する文字列 FROM 対象文字列)

LEADINGは先頭、TRAILINGは末尾、BOTHは両端を対象にします。方向を省略するとBOTH、除去する文字列を省略すると半角スペースが対象になります。

SELECT
    TRIM('  商品A  ') AS without_spaces,
    TRIM(LEADING '-' FROM '--商品A--') AS leading_removed,
    TRIM(TRAILING '-' FROM '--商品A--') AS trailing_removed,
    TRIM(BOTH '-' FROM '--商品A--') AS both_removed;

without_spaces	leading_removed	trailing_removed	both_removed
商品A	商品A--	--商品A	商品A

指定した文字列が続く間、対象の端から繰り返し取り除きます。ただし、途中にある文字は対象外です。例えば、TRIM(BOTH '-' FROM '--商-品--')の期待結果は商-品で、中央のハイフンは残ります。

複数文字を指定するときは、文字列単位で考える

除去対象に'ab'を指定すると、aとbをそれぞれ取り除くのではなく、abという並びを取り除きます。

SELECT
    TRIM(BOTH 'ab' FROM 'abab商品abab') AS repeated_removed,
    TRIM(BOTH 'ab' FROM 'ba商品ab') AS different_prefix;

repeated_removed	different_prefix
商品	ba商品

2つ目では、末尾のabは除かれますが、先頭のbaは順序が違うため残ります。また、既定のTRIMはタブ・改行・全角スペースをまとめて取り除く処理ではありません。何を取り除きたいのかを決めてから指定します。構文の詳細は、MySQL公式のTRIMの説明で確認できます。

SUBSTRING_INDEXで区切り文字の前後を取り出す

SUBSTRING_INDEXは、対象文字列、区切り文字、区切り文字を数える回数を指定します。

SUBSTRING_INDEX(対象文字列, 区切り文字, 回数)

回数が正なら左から数え、その区切り文字より左側を返します。負なら右から数え、その区切り文字より右側を返します。境界となった区切り文字自体は、返す部分に含めません。

SELECT
    SUBSTRING_INDEX('BOOK-2026-001', '-', 1) AS left_1,
    SUBSTRING_INDEX('BOOK-2026-001', '-', 2) AS left_2,
    SUBSTRING_INDEX('BOOK-2026-001', '-', -1) AS right_1,
    SUBSTRING_INDEX('BOOK-2026-001', '-', -2) AS right_2;

left_1	left_2	right_1	right_2
BOOK	BOOK-2026	001	2026-001

回数に2を指定しても、「2番目の要素だけ」が返るわけではありません。左から2つ目のハイフンまでの部分なので、BOOK-2026になります。

中央の2026だけを取り出すなら、まず左から2つ目のハイフンまでを取得し、その結果の最後の部分を取り出せます。

SELECT SUBSTRING_INDEX(
    SUBSTRING_INDEX('BOOK-2026-001', '-', 2),
    '-',
    -1
) AS middle_part;

この例の期待結果は2026です。ただし、「ハイフンで3つに分かれる」という入力形式を前提にした書き方なので、区切りが足りない値も確認しておきます。

区切り文字が足りない場合は元の文字列が返る

SELECT
    SUBSTRING_INDEX('BOOK', '-', 1) AS no_delimiter,
    SUBSTRING_INDEX('BOOK-2026', '-', 2) AS fewer_delimiters,
    SUBSTRING_INDEX('BOOK-2026-001', '-', 0) AS zero_count,
    SUBSTRING_INDEX('BOOK--001', '-', -2) AS repeated_delimiters;

no_delimiter	fewer_delimiters	zero_count	repeated_delimiters
BOOK	BOOK-2026	空文字 ''	-001

区切り文字が指定した回数に足りないと、元の文字列が返ります。回数0は空文字です。また、連続するハイフンはそれぞれ数えるため、4つ目の結果には先頭のハイフンが残ります。

「値が返ったから想定した形式だった」とは判断できないので、データ形式の確認とは分けて考えます。区切り文字の検索では大小文字も区別されます。詳しくは、MySQL公式のSUBSTRING_INDEXの説明を参照してください。

REGEXP_SUBSTRでパターンに一致する部分を取り出す

区切り文字の位置ではなく、文字列の形式で探したい場合はREGEXP_SUBSTRを使えます。最初の2つの引数が対象文字列とパターンで、残りは必要に応じて指定します。

REGEXP_SUBSTR(対象文字列, パターン)
REGEXP_SUBSTR(対象文字列, パターン, 開始位置, 一致回数, マッチ方法)

開始位置は検索を始める文字位置、一致回数は何番目の一致を取り出すか、という指定です。どちらも省略時は1になります。一致した部分の文字列を返し、一致がなければNULLを返します。

例えば、注文メモの中からBOOK-4桁の数字-3桁の数字という形式のコードを取り出してみます。

SELECT
    REGEXP_SUBSTR(
        '注文メモ: BOOK-2026-001 / BOOK-2026-002',
        'BOOK-[0-9]{4}-[0-9]{3}',
        1, 1, 'c'
    ) AS first_code,
    REGEXP_SUBSTR(
        '注文メモ: BOOK-2026-001 / BOOK-2026-002',
        'BOOK-[0-9]{4}-[0-9]{3}',
        1, 2, 'c'
    ) AS second_code;

first_code	second_code
BOOK-2026-001	BOOK-2026-002

[0-9]は半角数字、{4}と{3}はその繰り返し回数です。末尾の'c'は大小文字を区別する指定で、'i'なら区別しません。省略時の大小文字の扱いは、引数の照合順序などに依存します。

SELECT
    REGEXP_SUBSTR('book-2026-001', 'BOOK-[0-9]{4}-[0-9]{3}', 1, 1, 'c') AS sensitive,
    REGEXP_SUBSTR('book-2026-001', 'BOOK-[0-9]{4}-[0-9]{3}', 1, 1, 'i') AS insensitive,
    REGEXP_SUBSTR('コードなし', 'BOOK-[0-9]{4}-[0-9]{3}', 1, 1, 'c') AS no_match;

sensitive	insensitive	no_match
NULL	book-2026-001	NULL

大小文字を区別せずに一致しても、返るのは元の文字列の該当部分です。大文字へ変換されるわけではありません。

また、このパターンは文章の一部を探すものです。BOOK-2026-0012にも、先頭のBOOK-2026-001という部分が一致します。コード全体が指定の形式か確認したい場合は、先頭と末尾の条件も含めます。後半の比較例で、この書き方を使っています。

MySQL 8.4の正規表現はICUを使います。PHPの正規表現の記事で扱ったPCREとは実装が異なり、MySQLにはPHPの/.../uという書き方をそのまま渡しません。構文とオプションは、MySQL公式のREGEXP_SUBSTRの説明を確認してください。

[[:space:]]で空白文字を扱う

空白文字を表す文字クラスに[:space:]があります。ここでは、外側の角括弧と組み合わせた[[:space:]]の形で使います。半角スペースだけでなく、タブ・改行・復帰文字なども対象になるため、既定のTRIMが除去する半角スペースより広い範囲の文字を扱えます。

[[:space:]]+なら、空白文字が1個以上続く部分に一致します。反対に、外側の角括弧の先頭へ^を置いた[^[:space:]]+なら、空白文字以外が1個以上続く部分に一致します。例えば、空白で区切られた文字列から、それぞれの部分を取り出せます。

SELECT
    REGEXP_SUBSTR('商品A   商品B', '[^[:space:]]+') AS first_part,
    REGEXP_SUBSTR('商品A   商品B', '[^[:space:]]+', 1, 2) AS second_part;

この例の期待結果は、first_partが商品A、second_partが商品Bです。間のスペースが複数あっても、空白文字を含まない部分を順に取り出せます。なお、[^...]の内側にある^は否定の指定で、パターンの先頭に置く^とは役割が異なります。

元の値と加工後の値を並べて確認する

ここまでの関数を、少量の説明用データに適用してみます。WITH句でデータと整形処理を分けていますが、テーブルの作成や保存値の変更は行わないSELECT文です。

WITH sample_codes AS (
    SELECT 1 AS id, '  BOOK-2026-001  ' AS raw_code
    UNION ALL SELECT 2, 'PEN-2026-002'
    UNION ALL SELECT 3, 'UNKNOWN'
    UNION ALL SELECT 4, ''
    UNION ALL SELECT 5, NULL
    UNION ALL SELECT 6, 'book-2026-001'
), normalized_codes AS (
    SELECT id, raw_code, TRIM(raw_code) AS normalized_code
    FROM sample_codes
)
SELECT
    id,
    raw_code,
    normalized_code,
    REGEXP_SUBSTR(raw_code, '[^[:space:]]+') AS first_part,
    SUBSTRING_INDEX(normalized_code, '-', 1) AS category,
    REGEXP_SUBSTR(
        normalized_code,
        '^[A-Z]+-[0-9]{4}-[0-9]{3}$',
        1, 1, 'c'
    ) AS matched_code
FROM normalized_codes
ORDER BY id;

この例では、英大文字のカテゴリ、4桁の数字、3桁の数字をハイフンでつなぐ形式とします。パターンの^と$で先頭・末尾を指定し、改行を含まない1行のコードを対象にします。入力と期待結果は次のとおりです。

id	raw_code	normalized_code	first_part	category	matched_code
1	␠␠BOOK-2026-001␠␠	BOOK-2026-001	BOOK-2026-001	BOOK	BOOK-2026-001
2	PEN-2026-002	PEN-2026-002	PEN-2026-002	PEN	PEN-2026-002
3	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	NULL
4	空文字 ''	空文字 ''	NULL	空文字 ''	NULL
5	NULL	NULL	NULL	NULL	NULL
6	book-2026-001	book-2026-001	book-2026-001	book	NULL

1行目のraw_codeでは、半角スペース1個を␠で示しています。この記号は、実際の値に含まれる文字ではありません。

first_partは、元のraw_codeから空白文字以外が続く最初の部分を取り出します。1行目では先頭のスペースを飛ばしてBOOK-2026-001を取得できますが、コードの形式は判定しないので、UNKNOWNや小文字のコードもそのまま返ります。空文字には一致する部分がないため、first_partはNULLです。

UNKNOWNからcategoryを取得すると、そのままUNKNOWNが返ります。一方、指定した形式には一致しないため、matched_codeはNULLです。空文字もこのパターンには一致せず、NULLの入力は各関数でNULLになります。空文字とNULLを同じ値として扱わないようにします。

元の値を残して並べると、どの文字が除去されたか、どの部分が抽出されたかを追いやすくなります。WITH句の基本は、MySQLのWITH句入門の記事で説明しています。SQLを実行する操作は、MySQL Workbench入門の記事も参照してください。

実際のデータへ適用するときの注意点

今回の例は、用途が分かるシンプルな入力と固定のパターンに絞っています。実際のデータでは、NULLや空文字のほか、先頭・末尾の区切り文字、連続した区切り文字、全角文字や改行を含む値も確認しましょう。

特に正規表現は、列の文字セット・照合順序やマッチ方法によって結果が変わることがあります。MySQL 8.4の正規表現関数はバイナリ文字列を受け付けないため、BLOBなどへ直接適用する場合も、文字列として扱えるデータかを先に確認してください。

バックスラッシュを含むパターンでは、正規表現に加えてSQL文字列のエスケープも関係します。NO_BACKSLASH_ESCAPESの設定で扱いが変わるため、別の記事からパターンだけをコピーするより、MySQL公式の文字列リテラルの説明も確認するのがよいかと思います。今回のパターンは、半角数字を[0-9]で表し、バックスラッシュを使わない形にしています。

また、少量の文字列で結果が確認できても、大量の行へ同じ処理をかけたときの実行時間までは分かりません。まず対象を絞って結果を確認し、性能が問題になる場合は実行計画や処理時間も調べます。

両端の整形ならTRIM、区切り文字を使う抽出ならSUBSTRING_INDEX、形式を使う抽出ならREGEXP_SUBSTR。まずは元の値と処理後の値を並べるSELECTから試すと、それぞれの役割をつかみやすいかと思います。

参考資料

• MySQL 8.4 Reference Manual: String Functions and Operators
• MySQL 8.4 Reference Manual: Regular Expressions
• MySQL 8.4 Reference Manual: Connection Character Sets and Collations
• MySQL 8.4 Reference Manual: String Literals
• MySQL Server公式ソース: SUBSTRING_INDEX・TRIMの実装