MySQLで文字列の先頭の文字を削除し、残りの数字の合計を求める方法

MySQLで文字列の先頭の文字を削除し、残りの数字の合計を求める方法

カラムの値として保存された文字列が、「先頭に1文字＋その後に数字」という形式になっているケースがあります。例えば、以下のようなデータです。

J230
A130
C13

このようなデータから、先頭の文字を取り除き、残りの数字部分だけを合計したい場合は、SUBSTRING()関数とSUM()関数を組み合わせて使用します。

テーブルの作成

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

mysql> create table DemoTable761 (Price varchar(100));
Query OK, 0 rows affected (0.62 sec)

レコードの挿入

insertコマンドを使用して、テーブルにいくつかのレコードを挿入します。

mysql> insert into DemoTable761 values('J230');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable761 values('A130');
Query OK, 1 row affected (0.70 sec)
mysql> insert into DemoTable761 values('C13');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable761 values('D456');
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable761 values('B6');
Query OK, 1 row affected (0.13 sec)

レコードの確認

select文を使って、テーブル内のすべてのレコードを表示してみましょう。

mysql> select *from DemoTable761;

実行すると、次のような結果が出力されます。

+-------+
| Price |
+-------+
| J230  |
| A130  |
| C13   |
| D456  |
| B6    |
+-------+
5 rows in set (0.00 sec)

合計を求めるクエリ

MySQLで文字列から先頭の1文字を削除し、残りの数字の合計を求めるには、以下のクエリを実行します。SUBSTRING(Price, 2)によって2文字目以降の文字列が抽出され、その結果は自動的に数値として扱われるため、SUM()関数で合計を計算できます。

mysql> select sum(substring(Price,2)) from DemoTable761;

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

+-------------------------+
| sum(substring(Price,2)) |
+-------------------------+
|                     835 |
+-------------------------+
1 row in set (0.00 sec)

このように、230 + 130 + 13 + 456 + 6 の計算により、合計値「835」が得られました。先頭のプレフィックス文字（商品コードやカテゴリ識別子など）が付いた価格データを集計する際に、非常に便利なテクニックです。