BigQuery提取子串转float64时报Bad Double Value错误如何解决

问题根源 #

你提取的数字子串中包含肉眼不可见的Unicode控制字符，例如你给出的示例字符串里@和#之间的84‬末尾实际带有U+202C（双向格式弹出控制符），这类字符不会在显示时呈现，但不属于合法的数字字符，因此直接转换为float64时会触发Bad Double Value错误。

解决方案 #

方案1：清洗子串后再转换

在转换前使用正则替换清除所有非数字、小数点的无效字符，适配现有截取逻辑：

safe_cast(
  REGEXP_REPLACE(
    substr(tr.object_text, strpos(tr.object_text,'@')+1, (strpos(tr.object_text,'#')-(strpos(tr.object_text,'@')+1))),
    r'[^0-9.]',
    ''
  ) as float64
) AS V_Share

方案2：改用正则直接提取数字（更推荐）

硬编码字符位置截取的逻辑对字符串格式容错率极低，直接用正则匹配目标数字段更稳定：

-- 提取*和@之间的数字
safe_cast(REGEXP_EXTRACT(tr.object_text, r'\*([0-9.]+)@') as float64) AS FP_Share,
-- 提取@和#之间的数字
safe_cast(REGEXP_EXTRACT(tr.object_text, r'@([0-9.]+)#') as float64) AS V_Share,
-- 提取#之后的数字
safe_cast(REGEXP_EXTRACT(tr.object_text, r'#([0-9.]+)$') as float64) as Cust_Price

异常排查方法 #

如果需要确认子串是否包含无效字符，可运行以下查询验证：

SELECT 
  noCast_V_Share,
  LENGTH(noCast_V_Share) AS 实际长度,
  LENGTH(REGEXP_REPLACE(noCast_V_Share, r'[^0-9.]', '')) AS 清洗后长度
FROM tr
WHERE safe_cast(noCast_V_Share as float64) IS NULL

如果实际长度大于清洗后长度，即可确认子串中包含不可见的无效字符。

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