MySQL：解析并转换含数字与单位的字符串字段

当然可以实现这个需求！核心思路就是先把字符串里的数字（包括整数和小数）部分提取出来，再用CAST转换成你需要的数值类型（比如FLOAT、DECIMAL）。不过具体写法得看你使用的数据库，因为不同数据库的字符串处理函数差异挺大的，我给你整理几种主流场景的实现：

1. PostgreSQL 实现 #

PostgreSQL支持正则表达式的substring函数，可以直接匹配开头的数字部分：

SELECT 
  my_col,
  -- 提取开头的数字（含小数、负数）并转换为NUMERIC类型
  CAST(substring(my_col FROM '^-?\d+\.?\d*') AS NUMERIC) AS parsed_number
FROM my_table;

• 正则^-?\d+\.?\d*的含义：匹配开头的可选负号-?，接着是至少一个数字\d+，可选的小数点\.?，再加上可选的后续数字\d*，完美覆盖整数、小数、负数的情况。

2. MySQL/MariaDB 实现 #

如果你的MySQL版本在8.0及以上，可以用REGEXP_SUBSTR函数直接提取：

SELECT 
  my_col,
  CAST(REGEXP_SUBSTR(my_col, '^-?[0-9]+(\.[0-9]+)?') AS DECIMAL(10,5)) AS parsed_number
FROM my_table;

• 这个正则同样支持负数、整数和小数，转换为DECIMAL(10,5)可以控制精度，你可以根据业务调整参数。

3. SQL Server 实现 #

SQL Server可以结合PATINDEX和SUBSTRING来定位并截取数字部分：

SELECT 
  my_col,
  CASE 
    -- 先判断字符串是否包含数字，避免报错
    WHEN PATINDEX('%[0-9]%', my_col) > 0 THEN 
      CAST(SUBSTRING(my_col, 1, PATINDEX('%[^0-9.-]%', my_col + ' ') - 1) AS FLOAT)
    ELSE NULL
  END AS parsed_number
FROM my_table;

• PATINDEX('%[^0-9.-]%', my_col + ' ')用来找到第一个非数字、非小数点、非负号的位置，加空格是为了处理像"25L"这种数字和单位连在一起的情况，确保能准确定位。

4. 用专用函数简化（比如Snowflake） #

如果你用的是Snowflake这类支持NUM_PARSE函数的数据库，那就更简单了——这个函数会自动识别字符串中的数值部分：

SELECT 
  my_col,
  CAST(NUM_PARSE(my_col) AS FLOAT) AS parsed_number
FROM my_table;

• NUM_PARSE会忽略单位部分，直接提取里面的数字，不需要自己写正则，非常省心。

注意边界情况 #

• 如果字符串里有多个小数点（比如"12.34.56 GB"），上面的正则会提取到第一个小数点前的部分，你需要根据实际业务规则调整正则或者做额外处理。
• 确保字符串开头是数字（或负号+数字），如果有特殊情况（比如开头是单位），可以在CASE里增加判断逻辑避免转换报错。

内容的提问来源于stack exchange，提问作者bwrabbit