SQL如何处理varchar类型数量字段的多余小数点以统一数值格式

核心处理规则为：先统计字符串内小数点的总数量，若数量≥2则删除第一个出现的小数点，若数量≤1则直接返回原值，完全匹配需求场景。

MySQL 实现 #

基础兼容版（支持所有MySQL版本） #

SELECT 
  CASE 
    -- 统计小数点数量是否≥2
    WHEN LENGTH(Qty) - LENGTH(REPLACE(Qty, '.', '')) >= 2 
    THEN CONCAT(
      -- 截取第一个小数点之前的内容
      SUBSTRING(Qty, 1, LOCATE('.', Qty) - 1),
      -- 截取第一个小数点之后的所有内容
      SUBSTRING(Qty, LOCATE('.', Qty) + 1)
    )
    ELSE Qty 
  END AS formatted_qty
FROM your_table;

正则简化版（支持MySQL 8.0及以上版本） #

SELECT 
  CASE 
    WHEN LENGTH(Qty) - LENGTH(REPLACE(Qty, '.', '')) >= 2
    -- 仅替换第一个匹配到的小数点
    THEN REGEXP_REPLACE(Qty, '\\.', '', 1, 1)
    ELSE Qty
  END AS formatted_qty
FROM your_table;

SQL Server 实现 #

SELECT 
  CASE 
    WHEN LEN(Qty) - LEN(REPLACE(Qty, '.', '')) >= 2
    THEN CONCAT(
      SUBSTRING(Qty, 1, CHARINDEX('.', Qty) - 1),
      SUBSTRING(Qty, CHARINDEX('.', Qty) + 1, LEN(Qty))
    )
    ELSE Qty
  END AS formatted_qty
FROM your_table;

Oracle 实现 #

SELECT 
  CASE 
    WHEN LENGTH(Qty) - LENGTH(REPLACE(Qty, '.', '')) >= 2
    THEN SUBSTR(Qty, 1, INSTR(Qty, '.') - 1) || SUBSTR(Qty, INSTR(Qty, '.') + 1)
    ELSE Qty
  END AS formatted_qty
FROM your_table;

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