SQL Server CONVERT(NUMERIC(18,0), '') 失败但 CONVERT(INT, '') 成功？

SQL Server CONVERT(NUMERIC(18,0), '') fails but CONVERT(INT, '') succeeds?

提问by Zachary Scott

PRINT CONVERT(NUMERIC(18,0), '')

PRINT CONVERT(NUMERIC(18,0), '')

produces Error converting data type varchar to numeric.

产生 Error converting data type varchar to numeric.

However,

然而，

PRINT CONVERT(INT, '')

PRINT CONVERT(INT, '')

produces 0without error...

产生 0没有错误...

Question: Is there some SQL Server flag for this or will I need to do case statements for every varchar to numeric conversion? (aside from the obvious why?)

问题：是否有一些 SQL Server 标志，或者我是否需要为每个 varchar 到数字转换做 case 语句？（除了显而易见的原因？）

回答by SqlACID

Use ISNUMERIC

使用ISNUMERIC

declare @a varchar(20)
set @a = 'notanumber'
select case when isnumeric(@a) = 0 then 0 else convert(numeric(18,0),@a) end

回答by gbn

Empty string will convert to zero for float and int types, but not decimal. (And converts to 01 Jan 1900 for datetimes = zero). I don't know why.. it just is...

对于 float 和 int 类型，空字符串将转换为零，但不会转换为十进制。（并转换为 1900 年 1 月 1 日，日期时间 = 零）。我不知道为什么......它只是......

If you need decimal(18,0), use bigint instead. Or cast via float first

如果您需要 decimal(18,0)，请改用 bigint。或者先通过浮动进行投射

ISNUMERIC will accept -and .and 1.2E3as a number, but all fail to convert to decimal.

ISNUMERIC 将接受-and 和.and1.2E3作为数字，但都无法转换为十进制。

回答by Joe

ISNUMERIC doesn't alway work as you might expect: in particular it returns True for some values that can't subsequently be converted to numeric.

ISNUMERIC 并不总是像您期望的那样工作：特别是对于某些随后无法转换为数字的值，它返回 True。

This articledescribes the issue and suggests how to work around it with UDFs.

本文描述了该问题并建议如何使用 UDF 解决该问题。