varchar转换为数字:将数据类型varchar转换为数字的错误

I am trying to convert a column formatted in varchar to decimal(19,12) with the following line of code

我正在尝试使用以下代码将varchar格式的列转换为decimal(19,12)

ALTER TABLE [tablename]
ALTER COLUMN [columnname][format]

and get the following prompt:

并得到以下提示:

Msg 8114, Level 16, State 5, Line 25
Error converting data type varchar to numeric.

Has worked before like a charm. The issue here seems to be that the values in the column are 19 or so digit numeric values formatted as text.

以前的工作就像一种魅力。这里的问题似乎是列中的值是19左右的数字值格式化为文本。

I tried to create a new column, pasted shortened cell values (used the left() function) into it from the original column but that doesn't seem to do the trick either since the code above ends up occationally with the additional "Arithmetic overflow occurred." message.

我尝试从原始列中创建一个新的列，将缩短的单元格值(使用左()函数)粘贴到其中，但这似乎也没有起到什么作用，因为上面的代码以额外的“算术溢出发生”消息作为结果。

2 个解决方案

#1

When some of the rows have incorrect values, ALTER COLUMN would not work. A typical course of action goes like this:

当某些行有不正确的值时，ALTER列将不起作用。一个典型的做法是:

Add a new column of the desired type
添加所需类型的新列
Update the column with values that you would like to keep
用希望保留的值更新列
Drop the old column
把旧的列
Rename the new column
重命名新列

Step 2 would go like this:

第二步是这样的:

UPDATE MyTable
SET NewColumn =
    CASE WHEN ISNUMERIC(OldColumn)=1 AND DATALENGTH(OldColumn) <= 19 THEN
        CAST(OldColumn AS decimal(19,12))
    ELSE
        NULL
    END

You could also turn ANSI warnings off with SET ANSI_WARNINGS OFF command, which would let you run ALTER COLUMN ignoring data trunction errors. The drawback of this approach is that potential errors get ignored. On the other hand, when you do conversion explicitly with a CASE expression you have an option to supply an alternative value for the error case (I used NULL above, but you can put any number you want).

您还可以使用SET ansi_warning off命令关闭ANSI警告，这将允许您运行ALTER列，而忽略数据截断错误。这种方法的缺点是忽略了潜在的错误。另一方面，当您显式地使用一个CASE表达式进行转换时，您可以选择为错误情况提供一个替代值(我使用的是NULL，但是您可以添加任何您想要的数字)。

#2

Could you try to seperate your problem? This does work on SQL 2012:

你能把你的问题解决掉吗?这确实适用于SQL 2012:

set nocount on 
if object_id ('tempdb..#t1') is not null drop table #t1

create table #t1 (c1 varchar(100))

insert #t1 values ('1234567.8901234567890')
select * from #t1

alter table #t1
alter column c1 decimal(19,12) 

select * from #t1

If you play around a bit with the strings you easily can produce an arimetic overflow error. But 'Error converting data type varchar to numeric' needs character or empty sting.

如果对字符串进行一点处理，就很容易产生一个拟溢出错误。但是“将数据类型varchar转换为数字”需要字符或空刺。

Maybe you can try with your data?

也许你可以试试你的数据?

#1