SQL Server中TRY_CAST转换货币字符串返回NULL的问题排查与解决

要确认字符串中是否存在隐藏的特殊字符，你可以用以下几种SQL方法：

1. 逐个字符检查编码
   运行下面的语句，查看目标字符串每个字符的ASCII和Unicode编码，找出异常字符：

   DECLARE @targetStr NVARCHAR(100) = '12424.52‬'
   SELECT 
       SUBSTRING(@targetStr, num, 1) AS 字符,
       ASCII(SUBSTRING(@targetStr, num, 1)) AS ASCII编码,
       UNICODE(SUBSTRING(@targetStr, num, 1)) AS Unicode编码
   FROM (SELECT number AS num FROM master..spt_values WHERE type = 'P' AND number BETWEEN 1 AND LEN(@targetStr)) AS 数字序列
   

   对比正常字符串的输出结果，就能定位到导致转换失败的特殊字符及其编码值。

2. 对比长度与字节数
   通过LEN（字符数）和DATALENGTH（字节数）的差异判断是否存在隐藏字符：

   SELECT 
       LEN('12424.52‬') AS 异常字符串字符数,
       DATALENGTH('12424.52‬') AS 异常字符串字节数,
       LEN('12424.52') AS 正常字符串字符数,
       DATALENGTH('12424.52') AS 正常字符串字节数
   

   如果异常字符串的字符数/字节数比正常字符串多，说明存在肉眼不可见的隐藏字符。

根据排查结果，选择对应的清理方式：

1. 精准替换已知特殊字符
   如果你已经查到了特殊字符的Unicode编码（比如零宽空格U+202C对应的十进制是8236），直接用REPLACE替换掉：

   SELECT TRY_CAST(REPLACE('12424.52‬', NCHAR(8236), '') AS MONEY)
   

2. 过滤仅保留数字和小数点
   不管是什么特殊字符，只保留字符串中的数字和小数点，确保转换有效：

   • SQL Server 2017及以上版本（支持STRING_AGG）：

     DECLARE @targetStr NVARCHAR(100) = '12424.52‬'
     SELECT TRY_CAST(STRING_AGG(SUBSTRING(@targetStr, num, 1), '') WITHIN GROUP (ORDER BY num) AS MONEY)
     FROM (SELECT number AS num FROM master..spt_values WHERE type = 'P' AND number BETWEEN 1 AND LEN(@targetStr)) AS 数字序列
     WHERE SUBSTRING(@targetStr, num, 1) LIKE '[0-9.]'
     

   • 低版本SQL Server（用嵌套REPLACE清理非目标字符）：

     SELECT TRY_CAST(
         REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                 REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                     '12424.52‬', '`',''),'~',''),'!',''),'@',''),'#',''),
                     '$',''),'%',''),'^',''),'&',''),'*',''),
                     '(',''),')',''),'-',''),'_',''),'+','')
         AS MONEY)
     

     可以根据实际存在的特殊字符，继续扩展REPLACE的内容。

3. 尝试使用TRY_PARSE（针对格式兼容场景）
   如果特殊字符不影响格式识别，也可以试试带文化参数的TRY_PARSE：

   SELECT TRY_PARSE('12424.52‬' AS MONEY USING 'en-US')
   

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