如何使用SQL从格式化的字母数字字符串中提取第1和第2数字？

Question

weight列中的示例数据(来自3行数据)

+-------------------------+
| weight                  |
+-------------------------+
| Pump:398kg Motor:0kg    |
| Pump:448# Motor:997#    |
| Pump:355kg Motor:2495kg |
+-------------------------+

是否有方法分别从列中提取第一个数字和第二个数字？

备注

• 字符串总是以Pump:开头
• 字符串Motor:将始终位于中间
• 面额分隔符是#或kg，没有其他分隔符。
• 权重总是一个整数。

我的目标是将每个数字分成各自的列。

即

UPDATE table set weight_pump = ?, weight_motor = ?
UPDATE table set weight_pump = 398, weight_motor = 0

我可以使用PHP解决这个问题(选择每一行，执行REGEX提取数字，写回行)

但如果存在纯SQL解决方案的话，我要寻找的是一个纯SQL解决方案。最好是一个SQL语句，如果存在，但我不反对几个语句或中间步骤。

我已经看到了其他的答案，但是他们专注于从SQL中提取一个数字，我正在寻找更强大的东西。

注意:也可以通过两个步骤(一个用于"kg"，一个用于"#")简化查询。

Answer 1

这里有一个解决方案，使用substring_index()。

我喜欢这个函数，因为它不太依赖于特定的字符位置。

mysql> SELECT
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(weight, ':', -2), 'kg', 1), '#', 1) AS weight_pump,
  SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(weight, ':', -1), 'kg', 1), '#', 1) AS weight_motor 
FROM MyTable;

输出：

+-------------+--------------+
| weight_pump | weight_motor |
+-------------+--------------+
| 398         | 0            |
| 448         | 997          |
| 355         | 2495         |
+-------------+--------------+

这也适用于更新：

mysql> UPDATE MyTable
  SET weight_pump = SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(weight, ':', -2), 'kg', 1), '#', 1),
      weight_motor = SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(weight, ':', -1), 'kg', 1), '#', 1);

在这里阅读有关此函数的更多信息：substring-index