SQL中如何将含数字与文本的不一致值列转换为数值？

针对将带"M"后缀的混合文本数值转换为纯数字的需求，以下是主流SQL数据库的实现方案：

MySQL 实现 #

先移除"M"后缀，将剩余部分转为数值后乘以1000000（百万）：

SELECT 
  original_value,
  CAST(REPLACE(original_value, 'M', '') AS DECIMAL(10,2)) * 1000000 AS numeric_value
FROM your_table;

如果需要兼容其他后缀（如K代表千）或过滤无效值，可使用CASE判断：

SELECT 
  original_value,
  CASE 
    WHEN original_value LIKE '%M' THEN CAST(REPLACE(original_value, 'M', '') AS DECIMAL(10,2)) * 1000000
    WHEN original_value LIKE '%K' THEN CAST(REPLACE(original_value, 'K', '') AS DECIMAL(10,2)) * 1000
    ELSE NULL -- 可根据需求改为保留原值或抛出错误
  END AS numeric_value
FROM your_table;

PostgreSQL 实现 #

利用PostgreSQL的类型转换语法，简化转换流程：

SELECT 
  original_value,
  REPLACE(original_value, 'M', '')::numeric * 1000000 AS numeric_value
FROM your_table;

带多后缀判断的版本：

SELECT 
  original_value,
  CASE 
    WHEN original_value LIKE '%M' THEN REPLACE(original_value, 'M', '')::numeric * 1000000
    WHEN original_value LIKE '%K' THEN REPLACE(original_value, 'K', '')::numeric * 1000
    ELSE NULL
  END AS numeric_value
FROM your_table;

SQL Server 实现 #

使用CAST/转换函数处理字符串转数值：

SELECT 
  original_value,
  CAST(REPLACE(original_value, 'M', '') AS DECIMAL(10,2)) * 1000000 AS numeric_value
FROM your_table;

扩展判断版本：

SELECT 
  original_value,
  CASE 
    WHEN original_value LIKE '%M' THEN CAST(REPLACE(original_value, 'M', '') AS DECIMAL(10,2)) * 1000000
    WHEN original_value LIKE '%K' THEN CAST(REPLACE(original_value, 'K', '') AS DECIMAL(10,2)) * 1000
    ELSE NULL
  END AS numeric_value
FROM your_table;

注意事项 #

• 如果原字段存在空格或无关字符，先通过TRIM清理：REPLACE(TRIM(original_value), 'M', '')
• 根据实际数据调整DECIMAL(10,2)的精度，避免数值溢出
• 若要直接更新表中数据，以MySQL为例：

UPDATE your_table
SET target_column = CAST(REPLACE(original_value, 'M', '') AS DECIMAL(10,2)) * 1000000
WHERE original_value LIKE '%M';

内容的提问来源于stack exchange，提问作者Thuỳ Linh Lương