执行Update语句时出现varchar转numeric转换错误，请求协助排查

这个Error converting varchar to numeric错误我之前处理过好多次，本质就是字符串里藏着没法转成数字的内容，导致SQL的隐式转换翻车了。咱们一步步来拆解问题、排查脏数据，最后解决它：

你的Update语句里有两处可能触发转换失败的地方：

• 关联条件的隐式转换：s.customer_cis是varchar类型，k.dedupe_static是decimal类型。当执行JOIN时，SQL会自动把varchar转成decimal去匹配，如果s.customer_cis里有非数字内容（比如字母、符号、多余空格，甚至是空字符串），转换直接报错。
• 赋值操作的类型转换：把s.customer_identifier（varchar）赋值给customer_id_num（decimal）时，同样如果这个字段里存在无法转成数值的字符，也会触发错误。

首先得定位到底是哪些记录在搞事情，执行下面的查询就能揪出它们：

1. 检查关联字段的无效数据 #

找出s.customer_cis里没法转成decimal(20,0)的记录：

SELECT s.customer_cis
FROM [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804] s
WHERE TRY_CONVERT(decimal(20,0), s.customer_cis) IS NULL
AND s.customer_cis IS NOT NULL -- 排除空值（如果业务允许空值的话）

2. 检查赋值字段的无效数据 #

再检查s.customer_identifier里没法转成decimal(16,0)的记录：

SELECT s.customer_identifier
FROM [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804] s
WHERE TRY_CONVERT(decimal(16,0), s.customer_identifier) IS NULL
AND s.customer_identifier IS NOT NULL

根据排查出来的脏数据类型，你可以选下面的方式处理：

方案一：先清理脏数据（推荐） #

如果这些无效数据是业务上不需要的，直接删除或者修正：

• 删除无法转换的关联字段记录：

DELETE FROM [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804]
WHERE TRY_CONVERT(decimal(20,0), customer_cis) IS NULL
AND customer_cis IS NOT NULL

• 或者修正字段内容（比如去掉多余符号，根据你的业务规则调整）：

UPDATE [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804]
SET customer_cis = REPLACE(REPLACE(customer_cis, ' ', ''), '$', '') -- 示例：去掉空格和$符号
WHERE TRY_CONVERT(decimal(20,0), customer_cis) IS NULL
AND customer_cis IS NOT NULL

方案二：在Update里用安全转换跳过脏数据 #

如果不想删除数据，只想跳过那些转换失败的记录，可以用TRY_CONVERT来确保只有能成功转换的记录才被更新：

UPDATE dbo.keyona 
SET customer_id_num = TRY_CONVERT(decimal(16,0), s.customer_identifier)
FROM dbo.keyona k 
JOIN [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804] s 
  ON TRY_CONVERT(decimal(20,0), s.customer_cis) = k.dedupe_static
WHERE TRY_CONVERT(decimal(16,0), s.customer_identifier) IS NOT NULL

注意：这种方式会跳过转换失败的记录，也就是不会更新这些对应的数据，你得确认业务是否允许这种处理方式。

方案三：先处理字符串再转换（适合有特殊规则的场景） #

如果某些非数字字符是业务允许的（比如前缀0、后缀标识），可以先清理掉非数字字符再转换：

UPDATE dbo.keyona 
SET customer_id_num = CONVERT(decimal(16,0), 
  -- 只保留字符串中的数字字符，再截取前16位匹配decimal(16,0)的长度
  LEFT(LTRIM(RTRIM(REGEXP_REPLACE(s.customer_identifier, '[^0-9]', ''))), 16)
)
FROM dbo.keyona k 
JOIN [StorageDB - BAW].[dbo].[MI_NedLife_Mapper_Embedded_Linked_201804] s 
  ON CONVERT(decimal(20,0), REGEXP_REPLACE(s.customer_cis, '[^0-9]', '')) = k.dedupe_static
WHERE REGEXP_REPLACE(s.customer_identifier, '[^0-9]', '') <> '' -- 过滤掉处理后为空的记录

注：REGEXP_REPLACE只适用于SQL Server 2017及以上版本，如果是旧版本，可以用嵌套的REPLACE函数逐个去掉常见的非数字字符。

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