SQL Server char列混合值查询时varchar转int错误解决方法

错误根因 #

你写的过滤条件PM.PRODUCT_CATEGORIES06 = 1中，右值为int类型，按照SQL隐式类型转换规则，数据库会尝试把左值PRODUCT_CATEGORIES06（char(4)类型）的所有行值都转换为int类型再做比对，遇到'GRN '这类无法转为数字的字符串就会抛出转换失败错误。

可行解决方案 #

方案1：字符串匹配（最推荐，性能损耗最小） #

把过滤条件的右值改为字符串类型，避免触发隐式类型转换。因为字段是char(4)固定长度，存储数字1时会自带前导空格，建议先对字段做去空格处理再比对，兼容性更强：

SELECT RPO.REFERENCE_NUMBER, PM.PRODUCT_CATEGORIES06
FROM Table1 RPO
INNER JOIN Table2 PM ON PM.PRODUCT = RPO.PRODUCT
WHERE TRIM(PM.PRODUCT_CATEGORIES06) = '1'
  AND RPO.REFERENCE_NUMBER = 'REF1'
GROUP BY RPO.REFERENCE_NUMBER, PM.PRODUCT_CATEGORIES06

如果你的数据库不支持TRIM函数，可以替换为LTRIM(RTRIM(PM.PRODUCT_CATEGORIES06))。

方案2：安全转换后数字比对 #

如果需要严格按数字逻辑匹配（比如要匹配1、 1、0001这类都算1的场景），使用安全转换函数，转换失败会返回NULL不会抛出错误：

SELECT RPO.REFERENCE_NUMBER, PM.PRODUCT_CATEGORIES06
FROM Table1 RPO
INNER JOIN Table2 PM ON PM.PRODUCT = RPO.PRODUCT
WHERE TRY_CAST(LTRIM(RTRIM(PM.PRODUCT_CATEGORIES06)) AS INT) = 1
  AND RPO.REFERENCE_NUMBER = 'REF1'
GROUP BY RPO.REFERENCE_NUMBER, PM.PRODUCT_CATEGORIES06

注：TRY_CAST为SQL Server 2012及以上版本支持，低版本可以用CASE判断ISNUMERIC再转换，MySQL可用SAFE_CAST，Oracle可用TO_NUMBER加异常处理逻辑

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