在SQL Server中将varchar转换为decimal无法工作

Question

在SQL Server中将varchar转换为decimal无法工作

4

首先，我正在使用 SQL Server 2012 Express 64 位。

我正在尝试将一个 varchar 字段转换为 decimal 并乘以另一个 varchar。

这是我正在尝试在 SELECT 查询中运行的代码：

(CONVERT(decimal(12,2), COALESCE(Lineas.PARAM1, 0.00))) * (CONVERT(decimal(12,2), COALESCE(Lineas.PARAM2, 0.00)))  AS 'MULTIPARAM'

其中PARAM1和PARAM2是varchar类型。

这些字段上的数字分别为1和2。如此简单。我希望它返回3.00，但显示的错误是：

Mens. 8114, Nivel 16, Estado 5, Línea 1
Error al convertir el tipo de datos varchar a numeric。

即使PARAM1或PARAM2为null（被转换为0），或者它们是小数（例如：100.5，用点分隔），我也只想获得PARAM1 * PARAM2的结果。

我不明白为什么它不能正常工作...谢谢！

找到错误：

COALESCE(Lineas.PARAM1, 0.00)使其失败"将varchar类型数据转换为numeric"；而COALESCE(Lineas.PARAM1, 0)使其失败"将varchar '100.5'转换为int"。（这也不是我的意图）

我该怎么让它“工作”？

JATIN的解决方案：

SELECT (COALESCE(CONVERT(decimal(12,2), NULLIF(REPLACE(Lineas.PARAM1,' ',''),'')), 0.00)) * (COALESCE(CONVERT(decimal(12,2), NULLIF(REPLACE(Lineas.PARAM2,' ',''),'')), 0.00)) AS 'MULTIPARAM'

我所做的是首先替换所有空格，然后如果它是空字符串，则将其设置为NULL，其他的和以前一样。

- jinfo

Please translate to English - MusicLovingIndianGirl

1

选择 (CONVERT(decimal(12,2), COALESCE(1, 0.00))) * (CONVERT(decimal(12,2), COALESCE(2, 0.00))) 作为 'MULTIPARAM' 返回 2.0000。 - BIDeveloper

MusicLovingIndianGirl: 西班牙语不是日语或俄语，它在逻辑上是可读的“将varchar转换为数字错误”。 BIDeveloper: 谢谢但不起作用，同样的错误。 - jinfo

2个回答

1

你会为自己感到遗憾。英文错误信息：

Msg 8115, Level 16, State 8, Line 12
Arithmetic overflow error converting varchar to data type numeric.

样例数据;

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL DROP TABLE #TestData
GO
CREATE TABLE #TestData (ID int, Field1 varchar(10), Field2 varchar(10))
INSERT INTO #TestData
VALUES
(1,1,1)
,(2,1,2)
,(3,1,3)
,(4,1,4)
,(5,2,1)
,(6,2,2)
,(7,2,3)

查询（从你的合并中删除小数位）；

SELECT 
ID
,Field1
,Field2
,(CONVERT(decimal(12,2), COALESCE(a.Field1, 0))) * (CONVERT(decimal(12,2), COALESCE(a.Field2, 0)))  AS 'MULTIPARAM'
FROM #TestData a

结果;

ID  Field1  Field2  MULTIPARAM
1   1       1       1.0000
2   1       2       2.0000
3   1       3       3.0000
4   1       4       4.0000
5   2       1       2.0000
6   2       2       4.0000
7   2       3       6.0000

如果您想要结果保留两位小数，则使用以下内容；

SELECT 
ID
,Field1
,Field2
,CONVERT(decimal(12,2),(CONVERT(decimal(12,2), COALESCE(a.Field1, 0))) * (CONVERT(decimal(12,2), COALESCE(a.Field2, 0))))  AS 'MULTIPARAM'
FROM #TestData a

这是HTML代码，翻译为中文如下：

这将产生以下结果;

ID  Field1  Field2  MULTIPARAM
1   1       1       1.00
2   1       2       2.00
3   1       3       3.00
4   1       4       4.00
5   2       1       2.00
6   2       2       4.00
7   2       3       6.00

如果您的varchar字段中有小数，请尝试以下方法；

CONVERT(decimal(12,2),COALESCE(CAST(a.Field1 AS decimal(12,2)), 0) * COALESCE(CAST(a.Field2 AS decimal(12,0)), 0))  AS 'MULTIPARAM'

- Rich Benner

谢谢，我尝试了这两种方法，但仍然不起作用：(CONVERT(decimal(12,2),(CONVERT(decimal(12,2), COALESCE(PARAM1, 0))) * (CONVERT(decimal(12,2), COALESCE(PARAM2, 0)))) AS 'MULTIPARAM'(CONVERT(decimal(12,2), COALESCE(Lineas.PARAM1, 0))) * (CONVERT(decimal(12,2), COALESCE(LINEAS.PARAM2, 0))) AS 'MULTIPARAM'这两个查询给了我相同的错误： Mens. 245, Nivel 16, Estado 1, Línea 1 Error de conversión al convertir el valor varchar '100.5' al tipo de datos int. 这意味着转换不是十进制，而是整数；但我们正在使用“decimal”... - jinfo

你能在两个字段上运行ISNUMERIC(Field1)函数，以确保你的数据可以转换为int吗？如果该函数对于所有的行都返回1，那么数据就没有问题。如果你得到任何0，则代表你的数据存在问题。你提交的两个查询在提供的测试数据上都能正常工作。 - Rich Benner

它们都返回1，但使用COALESCE ISNUMERIC(COALESCE(Lineas.PARAM1, 0))，我得到了int错误。 - jinfo

尝试将a.Field1转换为decimal(12,2)类型，如果无法转换，则默认为0。然后将其与a.Field2转换为decimal(12,0)类型，如果无法转换，则默认为0相乘。最后，将结果命名为'MULTIPARAM'。 - Rich Benner

Msg. 8114，级别 16，状态 5，行 1 从 varchar 转换为 numeric 时出错 - jinfo

显示剩余6条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Jatin Patel · Accepted Answer

试试这个...

SELECT (COALESCE(CONVERT(decimal(12,2), Lineas.PARAM1), 0.00)) * (COALESCE(CONVERT(decimal(12,2), Lineas.PARAM2), 0.00))  AS 'MULTIPARAM'

问题出在这里...

SELECT COALESCE('1', 0.00)

如果你仍然遇到“将varchar数据类型转换为数字时出错”的错误，那么你的数据中可能包含除0-9以外的字符。