将 varchar 值转换为数据类型 int 时 SQL 转换失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35064675/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Conversion failed when converting the varchar value to data type int
提问by user979331
I have this query:
我有这个查询：
SELECT C.CustomerID,
Q.Question_ID,
Q.Department,
C.DueDate
FROM homefront.dbo.TPM_Questions_Default AS Q
LEFT OUTER JOIN homefront.dbo.CustomerQuestions AS C
ON Q.Question_ID = C.QuestionID
INNER JOIN tblCustomers T
ON CONVERT(INT, CONVERT(VARCHAR(MAX), T.Customer_No )) = C.CustomerID
WHERE C.DueDate <= GETDATE() AND C.DateCompleted IS NOT NULL
I just added in the INNER JOIN tblCustomers T ON CONVERT(INT, CONVERT(VARCHAR(MAX), T.Customer_No )) = C.CustomerID
我刚刚在 INNER JOIN tblCustomers T ON CONVERT(INT, CONVERT(VARCHAR(MAX), T.Customer_No )) = C.CustomerID
Now I get this error:
现在我收到这个错误：
Conversion failed when converting the varchar value 'C000432' to data type int.
回答by xan
You are converting (casting) a column containing what appears to be a character values to int values
您正在将包含看似字符值的列转换（转换）为 int 值
CONVERT(INT, CONVERT(VARCHAR(MAX), T.Customer_No ))
Since the value C000432is not a valid INT, you are getting an error. There's several things to consider:
由于该值C000432无效INT，您将收到错误消息。有几件事情需要考虑：
- Is customer number supposed to be a char / varchar data type and if so, are non integer characters expected / allowed in the data?
- If not:
- has some incorrect data snuck in (the C character) - test data?
- Why isn't it an int column to begin with (to make the DB enforce this)
- If so, why are you casting it to an int?
- Is the table you are joining to using a integer identifier?
- Do you need to strip off the leading 'C' Character before casting to an int?
- 客户编号是否应该是 char / varchar 数据类型，如果是这样，数据中是否需要/允许非整数字符？
- 如果不：
- 有一些不正确的数据潜入（C 字符） - 测试数据？
- 为什么不是以 int 列开头（使数据库强制执行此操作）
- 如果是这样，为什么要将其转换为 int？
- 您要加入的表是否使用整数标识符？
- 您是否需要在转换为 int 之前去掉前导的“C”字符？
