如何提取含货币前缀的字符串子串转为数值执行乘法运算

报错根因 #

截取到的数值字符串中包含千位分隔符,，SQL转换数值类型时无法识别该符号，直接转换会抛出类型转换失败错误。

修正代码 #

固定字符串测试版

SELECT CONVERT(DECIMAL(18,2), REPLACE(SUBSTRING('AUD 1,200.50', 4, 10), ',', '')) * 0.019

SAP B1动态字段版

SELECT CONVERT(DECIMAL(18,2), REPLACE(SUBSTRING($[$22.0.0], 4, 10), ',', '')) * 0.019

兼容性优化建议 #

• 转换decimal时建议明确指定精度和标度（例如DECIMAL(18,2)），避免默认精度带来的数值截断或精度损失
• 如果截取后的字符串存在首尾空白，可以嵌套TRIM函数清理冗余字符：

  SELECT CONVERT(DECIMAL(18,2), REPLACE(TRIM(SUBSTRING($[$22.0.0], 4, 10)), ',', '')) * 0.019
  

• 如果货币前缀长度不固定（例如存在2位、3位不同长度的货币代码），可以通过定位空格位置动态截取数值部分，适配性更强：

  SELECT CONVERT(DECIMAL(18,2), REPLACE(SUBSTRING($[$22.0.0], CHARINDEX(' ', $[$22.0.0]) + 1, LEN($[$22.0.0])), ',', '')) * 0.019
  

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