mysql - 从 mySQL 列字符串值中提取数值

Question

我在 mySQL 数据库中有一个“人”列，它以逗号分隔的字符串形式表示人的年龄和体重。

示例:“24,175”

我希望能够分离和提取这些值并将它们转换为数字。

示例:将“24,175”转为

24岁
175作为重量

这样我就可以编写类似于以下内容的查询

SELECT person 
FROM TABLE
WHERE age>140 OR weight>1000

我希望能够检查不可能的值。即年龄>140 或体重>1000。

我无法修改我正在使用的表/环境

我只能访问查询。

我正在考虑这样解决

查找逗号所在的索引。 CHARINDEX(',',人)

使用 LEFT 、 RIGHT、CAST 和 CHARINDEX(',',person) 将字符串拆分为子字符串

使用 CAST(age AS INT) CAST(weight AS INT) 将年龄子字符串和体重子字符串转换为数字

SELECT person
FROM TABLE
WHERE CAST(LEFT(person,CHARINDEX(',',person) AS INT)>150 OR CAST(RIGHT(person,CHARINDEX(',',person) AS INT) >1000

如果我做错了什么，请纠正我。mySQL 是否可以使用/支持所有功能？ (右、左、CHARINDEX)这行得通吗？

异常(exception):此列的另一个值可能是“未知”。如果我们尝试检查 的索引(如果字符串中不存在)，这会导致错误吗？有没有办法在结果中包含“未知”情况并让它输出“错误，无法识别人员”消息

Answer 1

您还可以使用 SUBSTR_INDEX 拆分 is，如下所示:

MariaDB [yourschema]> SELECT * FROM spliit;
+----+--------+
| id | d      |
+----+--------+
|  1 | 24,175 |
+----+--------+
1 row in set (0.03 sec)



MariaDB [yourschema]> SELECT
    ->     SUBSTRING_INDEX(d, ',', 1) AS age
    ->   , SUBSTRING_INDEX(d, ',', -1) AS weight
    ->
    -> FROM spliit;
+------+--------+
| age  | weight |
+------+--------+
| 24   | 175    |
+------+--------+
1 row in set (0.00 sec)

MariaDB [yourschema]>

示例

是的，你可以直接在MySQL中使用它进行计算

MariaDB [yourschema]> SELECT
    ->     SUBSTRING_INDEX(d, ',', 1)  + 2 AS age
    ->   , SUBSTRING_INDEX(d, ',', 1)  * 12 AS `month`
    ->   , SUBSTRING_INDEX(d, ',', -1) + 3 AS weight
    -> FROM spliit;

+------+-------+--------+
| age  | month | weight |
+------+-------+--------+
|   26 |   288 |    178 |
+------+-------+--------+
1 row in set, 1 warning (0.03 sec)

MariaDB [yourschema]>