BigQuery/GoogleSQL中如何仅替换多小数点的第一个小数点

我现在需要清理杂乱字符串提取有效数字，现有逻辑大部分能正常工作，但遇到多小数点的情况时，没法正确处理——需求是当字符串有多个小数点时，仅保留最后一个，移除前面所有的小数点。

现有处理代码 #

TRIM(REGEXP_REPLACE(CASE
  WHEN column = "-" THEN "0"
  WHEN column = "o" THEN "0"
  WHEN column IS NULL THEN "0"
  WHEN column LIKE '%o%' THEN REPLACE (column, 'o','0')
  WHEN column LIKE '%,%' THEN REPLACE (column, ',', '.')
  WHEN column LIKE '%,%' THEN REGEXP_REPLACE(column, r'\.', '')
  WHEN column LIKE '%/%' THEN "0"
  WHEN column LIKE '%kg%' THEN REPLACE (column, 'kg', '')
  WHEN LENGTH(column) - LENGTH(REPLACE(column, '.', '')) = 1 THEN column
  WHEN REGEXP_EXTRACT(column, 'n') IS NOT NULL THEN "0"
  WHEN REGEXP_EXTRACT(column, 'tb') IS NOT NULL THEN "0"
  WHEN REGEXP_EXTRACT(column, 'REF') IS NOT NULL THEN "0"
  ELSE column
  END,r's/.//', '')) AS Column

当前测试结果与预期差异 #

可以看到行2和行5的多小数点场景没达到预期，需要调整逻辑。

修正后的处理方案 #

首先要解决现有CASE的两个问题：

1. 重复的WHEN column LIKE '%,%'条件，第二个永远不会执行
2. 缺少多小数点的针对性处理

核心思路

先完成所有基础替换（特殊值转0、o换0、逗号换点、移除kg），再用正则处理多小数点：匹配所有后面还存在小数点的.，将其替换为空，这样就只保留最后一个小数点。

修正后的SQL代码

TRIM(
  REGEXP_REPLACE(
    CASE
      -- 先处理直接转0的特殊情况
      WHEN column IS NULL OR column = "-" OR column = "o" OR column LIKE '%/%' 
           OR REGEXP_CONTAINS(column, r'n|tb|REF') THEN "0"
      -- 基础替换：o换0，逗号换点，移除kg
      ELSE REPLACE(REPLACE(REPLACE(column, 'o', '0'), ',', '.'), 'kg', '')
      END,
    -- 关键正则：移除所有后面还有小数点的点，只留最后一个
    r'\.(?=.*\.)', ''
  )
) AS cleaned_column

正则解释

r'\.(?=.*\.)' 是正向预查的正则：

• \. 匹配小数点
• (?=.*\.) 表示这个小数点后面必须还有至少一个小数点
• 这样所有符合条件的前面的小数点都会被替换为空，最后一个小数点因为后面没有其他小数点，会被保留

验证结果 #

用修正后的代码跑测试用例，所有场景都能得到预期输出：

• 行2的45.856,90先转成45.856.90，再通过正则移除第一个.，得到45856.90
• 行5的123.45.67移除前两个.中的第一个，得到12345.67
• 其他场景保持原有正确逻辑不变

内容的提问来源于stack exchange，提问作者ROM-Cloudia