MySQLでテキストフィールドから数字のみを抽出する方法を解説

MySQLでは、テキスト型（TEXT）のカラムに保存されたデータに、数字以外の記号や文字が混在していることがあります。例えば、ハイフン（-）、カンマ（,）、スペース、引用符などが含まれる場合、それらを取り除いて数字だけを抽出したいケースは少なくありません。

本記事では、REPLACE関数を組み合わせることで、テキストフィールドから数字のみを抽出する方法を、実際のSQL文と実行結果とともにわかりやすく解説します。

1. サンプルテーブルを作成する

まず、テキスト型のカラムを持つテーブルを作成します。

mysql> create table DemoTable
(
    Number text
);
Query OK, 0 rows affected (0.49 sec)

2. テストデータを挿入する

次に、INSERTコマンドを使って、数字と記号が混在したレコードを挿入します。

mysql> insert into DemoTable values('7364746464,-');
Query OK, 1 row affected (0.21 sec)

mysql> insert into DemoTable values('-,8909094556');
Query OK, 1 row affected (0.23 sec)

3. テーブルの内容を確認する

SELECT文ですべてのレコードを表示してみましょう。

mysql> select *from DemoTable;

実行結果は以下の通りです。数字の前後に「-」や「,」が含まれていることが確認できます。

+--------------+
| Number       |
+--------------+
| 7364746464,- |
| -,8909094556 |
+--------------+
2 rows in set (0.00 sec)

4. REPLACE関数をネストして数字のみを抽出する

数字以外の文字を除去するには、REPLACE関数を入れ子（ネスト）にして、不要な文字を順番に空文字列に置き換えます。

mysql> select replace(replace(replace(replace(Number, '-', ''), ' ', ''), '"',''),',','') as Result from DemoTable;

このクエリでは、内側から順に以下の処理を行っています。

• ハイフン（-）を削除
• スペース（半角空白）を削除
• ダブルクォート（"）を削除
• カンマ（,）を削除

実行結果は以下の通りです。数字のみがきれいに抽出されました。

+------------+
| Result     |
+------------+
| 7364746464 |
| 8909094556 |
+------------+
2 rows in set (0.00 sec)

補足：より柔軟な方法について

上記の方法は、除去したい文字の種類があらかじめ分かっている場合に有効です。しかし、含まれる文字が不定の場合や、英字もまとめて除去したい場合は、REPLACE関数のネストが長くなりすぎて可読性が低下します。

そのような場合は、以下のような代替手段も検討できます。

• REGEXP_REPLACE関数：MySQL 8.0以降で利用可能。REGEXP_REPLACE(Number, '[^0-9]', '')のように正規表現で「数字以外」を一括削除でき、シンプルに記述できます。
• アプリケーション側での処理：取得したデータをPHPやPythonなどのプログラム側で整形する方法もあります。

使用しているMySQLのバージョンに応じて、最適な方法を選択してください。