尝试将字符串转换为int时转换失败 [英] Conversion fails when trying to Convert string to int

问题描述

我在字符串列中有一些销售数字，我需要将其转换为某种格式，以便我可以相互计算它们但我在尝试转换它时会出现此错误。

将varchar值'-6.353,35'转换为数据类型int时转换失败。

我不允许通过四舍五入来赔钱。它不会发生变化，但只要我没有将它们四舍五入，我会转换为什么类型。你的想法是什么？

例如我有-6.353,35和300,30而且我想把它们相加-6.053,05

解决方案

首先，您要将此值转换为十进制值，而不是 int ，因为否则会在小数点后丢失任何内容。

这里的问题是存储值时使用的分隔符。 / p>

使用您的号码，您有点。代表千位分隔符和逗号，表示小数点。

要使您能够将值转换为有效的小数，您需要使用SQL Server可以处理的格式。

一种简单的方法是在尝试转换值之前删除/替换问题字符：

  DECLARE @val AS VARCHAR（10）=' -  6.353,35'
 
  - 步骤1：删除千位分隔符
 SET @val = REPLACE（@val，'。'，' '）
 SELECT @val AS RemoveThousandSeparator 
 
  - 步骤2：用小数点替换小数点分隔符
 SET @val = REPLACE（@val，'，'，'。' ）
 SELECT CONVERT（DECIMAL（18,2），@ val）AS DecimalPointAdded 
 
  - 在一个语句中执行：
 SET @val ='-6.353,35 '
 SELECT CONVERT（DECIMAL（18,2），
 REPLACE（REPLACE（@val，'。'，''），'，'，'。'））AS NumericeRepresentation 
  

除此之外，您最好以正确的格式存储数值。首先要避免这种解决方法。

I have some sales numbers in a string column that I need to convert to some format so that i can calculate them with each other but I get this error while trying to convert them.

Conversion failed when converting the varchar value '-6.353,35' to data type int.

I'm not allowed to lose any money by rounding it up. It doesnt mather but in what type i convert as long as im not rounding them up. What's your thoughts?

For example i have -6.353,35 and 300,30 and i want to sum them too -6.053,05

解决方案

Firstly, you want to convert this to a decimal value, not an int, as you will lose anything after decimal point otherwise.

The problem here is the separators that are being used when storing your values.

With your numbers, you have points . to represent thousand separators and commas , to represent decimal points.

To enable you to convert the value to a valid decimal you need to have it in a format that SQL Server can process.

A simple way to do this would be to remove/replace the problem characters before trying to convert the value:

DECLARE @val AS VARCHAR(10) = '-6.353,35'

-- step 1: remove thousand separator
SET @val = REPLACE(@val, '.', '')
SELECT @val AS RemoveThousandSeparator

-- step 2: replace decimal separator with decimal point
SET @val = REPLACE(@val, ',', '.')    
SELECT CONVERT(DECIMAL(18,2), @val) AS DecimalPointAdded

-- to do it in one statement:
SET @val = '-6.353,35'
SELECT CONVERT(DECIMAL(18,2),
               REPLACE(REPLACE(@val, '.', ''), ',', '.')) AS NumericeRepresentation

Aside from this, you would be much better off storing numeric values in the correct format in the first place to avoid this kind of workaround.

这篇关于尝试将字符串转换为int时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！