代码之家 › 专栏 › 技术社区 › Kyle B.

SQL IsNumeric返回True,但SQL报告“转换失败”

tsql sql-server sql

Kyle B. · 技术社区 · 16 年前

假设以下数据:

Column1 (data type: varchar(50))
--------
11.6
-1
1,000
10"    
Non-Numeric String

我有一个查询,它从这个列中提取数据,并想确定该值是否是一个数字,然后在我的查询中返回它。因此,我正在做以下工作

SELECT CASE
       WHEN IsNumeric(Replace(Column1, '"', '')) = 1 THEN Replace(Column1, '"', '')
       ELSE 0
   END AS NumericValue

将varchar值“11.6”转换为数据类型int时,转换失败。

为什么?我也试着强制施展:

SELECT CASE
       WHEN IsNumeric(Replace(Column1, '"', '')) = 1 THEN cast(Replace(Column1, '"', '') AS float)
       ELSE 0
   END AS NumericValue

将数据类型varchar转换为float时出错。

8 回复 | 直到 6 年前

Quassnoi 16 年前

您需要将逗号替换为句点:

CAST(REPLACE(column, ',', '.') AS FLOAT)

SQL Server 输出使用区域设置定义的十进制分隔符,但在中除句点外不支持任何内容 CAST s转换为数字类型。

C B dkretz 7 年前

首先将字符串转换为货币,然后将其转换为任何其他数字格式,因为货币类型始终提供真实的数字字符串。你永远不会看到错误。

在您的查询中尝试以下内容,您就会知道我在说什么。两者都将返回2345.5656。Money数据类型四舍五入到小数点后4位,因此强制转换导致四舍五入到小数点后4位。

SELECT CAST('2,345.56556' as money), CAST('$2,345.56556' as money)

Cast(Cast('2344'作为货币)作为float)可以完美地工作,或者十进制(7,2)的cast(cast('2344'为货币)也会起作用。

甚至cast(cast('2345.56556'作为money)作为int也可以完美地将其舍入到最接近的整数。

brendan 16 年前

SQL isnumeric存在许多问题。例如:

select isnumeric('1e5')

这将返回1,但在许多语言中,如果尝试将其转换为数字,则会失败。更好的方法是使用需要检查的参数创建自己的用户定义函数:

http://www.tek-tips.com/faqs.cfm?fid=6423

dotjoe 16 年前

当输入表达式的计算结果为有效整数、浮点数、货币或十进制类型时,ISNUMERIC返回1;

Lieven Keersmaekers 16 年前

凯尔,

我认为这解决了问题。问题在于ELSE子句将结果初始化为整数。通过对FLOAT进行显式类型转换并添加Quassnoi的建议,它似乎起到了作用。

DECLARE @MyTable TABLE (Column1 VARCHAR(50))
INSERT INTO @MyTable VALUES('11.6')
INSERT INTO @MyTable VALUES('-1')
INSERT INTO @MyTable VALUES('1,000')
INSERT INTO @MyTable VALUES('10"    ')
INSERT INTO @MyTable VALUES('Non-Numeric String')

SELECT CASE WHEN ISNUMERIC(REPLACE(Column1,'"','')) = 1 THEN REPLACE(REPLACE(Column1,'"',''), ',', '.') ELSE CAST(0 AS FLOAT) END
FROM @MyTable

当做
利芬

Thiem Nguyen 13 年前

IsNumeric(' ') 也返回1,但随后强制转换为int。上面Brendan说写你自己的函数。他是对的。

user2284133 9 年前

此解决方案并非在所有情况下都有效(特别是带有货币和/或千分隔符的数字)。将指数表示连接到由字符串表示的数字的末尾…从这里开始,ISNUMERIC()可以正常工作。举例如下:

-- CURRENT ISNUMERIC RESULTS
SELECT ISNUMERIC('11.6'); --1
SELECT ISNUMERIC ('-1'); --1
SELECT ISNUMERIC('1,000'); --1
SELECT ISNUMERIC('10"'); --0
SELECT ISNUMERIC('$10'); --1

-- NEW ISNUMERIC RESULTS
SELECT ISNUMERIC('11.6'+'e+00'); --1
SELECT ISNUMERIC ('-1'+'e+00'); --1
SELECT ISNUMERIC('1,000'+'e+00'); --0
SELECT ISNUMERIC('10"'+'e+00'); --0
SELECT ISNUMERIC('$10'+'e+00'); --0

这至少使使用REPLACE()函数的格式标准化。

Hung Doan 9 年前

我刚刚遇到这个问题。

CONVERT(numeric, CONVERT(money, '.'))

注:

它是支持 SQL Server 2008 或以上。
钱 is:-922337203685477.5808至922337203685477.5807- 四位小数