SQL查询报错：varchar值'0:sval:'转换为int数据类型问题求助

报错核心是godValidatorSplit自定义函数存在隐式类型转换问题：当函数入参@isInputHex为非0值时，CASE表达式的分支分别返回数字类型（bigint/int）和字符串类型，SQL Server会自动将所有分支返回值向优先级更高的数字类型转换，当分割出的字符串存在0:sval:这类非数字内容时，转换失败触发报错。

• 先确认视图[RS].[REPORTS].[V_VALIDATOR_DIAGNOSTIC_EVENT_EXPLODED]调用godValidatorSplit时传入的@isInputHex值：如果该参数为非0，就会触发HEX转数字逻辑，进而触发隐式转换。
• 定位脏数据：查询你传入函数作为@inputString的原始字段，找到包含非十六进制字符的记录，参考语句如下（替换其中的原始分割字段为实际字段名）：

SELECT 原始分割字段 FROM [RS].[REPORTS].[V_VALIDATOR_DIAGNOSTIC_EVENT_EXPLODED]
WHERE TXN_DATE_TIME BETWEEN '2021-09-28 22:13:36' AND '2021-09-28 22:13:37'
AND IS_OK=0
AND 原始分割字段 LIKE '%:%'

• 验证函数问题：直接传入包含特殊字符的字符串和@isInputHex=1调用函数，确认是否复现报错：

SELECT * FROM REPORTS.godValidatorSplit('0:sval:,test,123',1)

根据你的业务场景选择以下方案即可：

• 方案1：修改函数统一返回类型，从根源避免隐式转换。将CASE中所有数字返回值转成字符串，保证所有分支返回类型一致，修改后函数代码如下：

FUNCTION [REPORTS].[godValidatorSplit] (@inputString varchar(max), @isInputHex tinyint)
RETURNS @return TABLE (val varchar(max))
AS
BEGIN
    DECLARE @xml xml
    SET @xml = CONVERT(xml,'<v>'+
    REPLACE(REPLACE(@inputString,',','</v><v>'),char(10),'')
    +'</v>')
    
    INSERT INTO @return(val)
    SELECT  CASE    
                WHEN (@isInputHex<>0) 
                THEN
                    CASE 
                        WHEN (v.value('.','varchar(MAX)') NOT LIKE '%[^a-fA-F0-9]%')
                        THEN CAST(convert(bigint, convert(varbinary, '0x' + RIGHT('00000000'+v.value('.','varchar(MAX)'),8), 1)) AS VARCHAR(MAX))
                        ELSE '0'
                    END
                ELSE v.value('.','varchar(MAX)')
            END
    FROM @xml.nodes('/v') as a(v)

    RETURN
END

• 方案2：如果业务要求返回数字类型，将函数返回表的val字段类型改为bigint，同时在调用函数前提前过滤掉包含非十六进制字符的脏数据。
• 方案3：如果当前业务场景不需要做HEX转数字，修改视图调用godValidatorSplit时传入的@isInputHex参数为0即可。

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