SQL 如何解决将数据类型 varchar 转换为数字的错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39248826/
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
How to solve error converting data type varchar to numeric
提问by Thiru
SELECT
A.SETMOD, B.DESCRP
FROM
PMS.PSBSTTBL A
JOIN
PMS.PR029TBL B ON A.SETMOD = B.PAYMOD
Paymodis of datatype VARCHAR, SETMODof type decimal
Paymod是数据类型VARCHAR，SETMOD类型decimal
回答by Gordon Linoff
A jointo two columns of different types is a really, really bad idea. Sometimes, though, other people design database systems, and we don't have a choice.
一join到两列不同类型的列是一个非常非常糟糕的主意。然而，有时，其他人设计数据库系统，而我们别无选择。
You have two choices, basically: convert both to numbers or both to strings. You are getting the error because in mixed type expressions, SQL Server converts to the more "restrictive" type (to simplify the logic). So, it converts the string to a number.
您有两种选择，基本上：将两者都转换为数字或都转换为字符串。您收到错误是因为在混合类型表达式中，SQL Server 转换为更“限制性”的类型（以简化逻辑）。因此，它将字符串转换为数字。
In SQL Server 2012+, I would suggest try_convert()for the conversion to a number:
在 SQL Server 2012+ 中，我建议try_convert()转换为数字：
SELECT A.SETMOD, B.DESCRP
FROM PMS.PSBSTTBL A JOIN
PMS.PR029TBL B
ON A.SETMOD = TRY_CONVERT(DECIMAL(?, ?), B.PAYMOD);
The ?,?should be the scale/precision of SETMOD.
本?,?应该是规模/精度SETMOD。
Of course, you could also force the conversion in the other direction:
当然，您也可以强制向另一个方向转换：
SELECT A.SETMOD, B.DESCRP
FROM PMS.PSBSTTBL A JOIN
PMS.PR029TBL B
ON CAST(A.SETMOD AS VARCHAR(255)) = B.PAYMOD;
This conversion will succeed, so you don't need TRY_CONVERT().
此转换将成功，因此您不需要TRY_CONVERT().
回答by Unnikrishnan R
Best thing is to convert the decimal to string and compare it with the string value.. make necessary adjustments on the decimal part.. :)
最好的办法是将小数转换为字符串并将其与字符串值进行比较..对小数部分进行必要的调整..:)
SELECT
A.SETMOD, B.DESCRP
FROM
PMS.PSBSTTBL A
JOIN
PMS.PR029TBL B ON CONVERT(VARCHAR(50),A.SETMOD) = B.PAYMOD
回答by Hitesh Thakor
SELECT
A.SETMOD, B.DESCRP
FROM
PMS.PSBSTTBL A
JOIN
PMS.PR029TBL B ON A.SETMOD =convert(decimal, B.PAYMOD)
