MySQLでvarchar列から文字列部分を削除し数値のみを抽出する方法｜UPDATE文とLEFT・INSTR関数の活用

MySQLでは、数値と文字列が混在するvarchar型の列から、文字列部分を取り除いて数値だけを残したいケースがあります。本記事では、UPDATE文とLEFT()関数・INSTR()関数を組み合わせて、既存の列データを一括更新する方法を解説します。

1. テーブルを作成する

まず、サンプル用のテーブルを作成します。

mysql> create table DemoTable
(
    Download varchar(100)
);
Query OK, 0 rows affected (0.53 sec)

2. レコードを挿入する

INSERTコマンドを使って、数値と単位が組み合わさったデータを登録します。

mysql> insert into DemoTable values('120 Gigabytes');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable values('190 Gigabytes');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('250 Gigabytes');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('1000 Gigabytes');
Query OK, 1 row affected (0.12 sec)

3. 現在のデータを確認する

SELECT文でテーブル内の全レコードを表示します。

mysql> select *from DemoTable;

実行結果は以下の通りです。

+----------------+
| Download        |
+----------------+
| 120 Gigabytes  |
| 190 Gigabytes  |
| 250 Gigabytes  |
| 1000 Gigabytes |
+----------------+
4 rows in set (0.00 sec)

4. 文字列部分を削除してデータを更新する

既存の列データを更新し、「Gigabytes」などの文字列部分を削除するには、以下のクエリを実行します。

mysql> update DemoTable set Download=LEFT(Download, INSTR(Download, ' ') - 1);
Query OK, 4 rows affected (0.16 sec)
Rows matched: 4 Changed: 4 Warnings: 0

クエリの仕組み

• INSTR(Download, ' ')：列の値の中で、最初に半角スペースが出現する位置を返します。
• LEFT(Download, 位置 − 1)：そのスペースの直前までの文字列、つまり数値部分だけを抽出します。

5. 更新結果を確認する

もう一度テーブルのレコードを確認してみましょう。

mysql> select *from DemoTable;

実行結果は以下の通りです。文字列部分が削除され、数値のみが残っていることがわかります。

+----------+
| Download |
+----------+
| 120      |
| 190      |
| 250      |
| 1000     |
+----------+
4 rows in set (0.00 sec)

このように、LEFT()とINSTR()を組み合わせることで、varchar列に含まれる不要な文字列を簡単に除去できます。ただし、スペースを含まないデータが存在する場合、INSTR()は0を返すため意図しない結果になる可能性がある点には注意が必要です。