T-SQL - 判断一个值是否为整数

Question

我想确定一个值是否为整数（类似于.NET中的TryParse）。不幸的是，ISNUMERIC 不适合我，因为我只想解析整数而不是每一种数字。是否有像 ISINT 这样的东西存在？

下面是一些代码，以便说明清楚。如果MY_FIELD 不是整数，那么这段代码将会失败：

SELECT @MY_VAR = CAST(MY_FIELD AS INT)
FROM MY_TABLE
WHERE MY_OTHER_FIELD = 'MY_FILTER'

谢谢

- zafeiris.m

---

更新：较新版本的SQL具有TRY_CONVERT函数。 - Nick.McDermaid

从 SQL-Server-2012 版本开始，可以使用 TRY_CAST(my_field AS integer) 来将 my_field 转换为整数类型。无需额外处理错误异常情况。 - Stefan Steiger

Answer 1

这里有一篇博客文章，描述了如何创建一个IsInteger UDF。

基本上，它建议在值后面添加'.e0' 并使用IsNumeric。这样，任何已经有小数点的东西现在就有了两个小数点，导致IsNumeric为false，任何已经以科学计数法表示的东西也会被e0无效化。

- Sam DeHaan

---

博客文章中展示的函数存在多个缺陷。请勿使用！！！这些输入返回了一个明显不正确的正数结果：SELECT /超过18个字符-函数应接受MAX数据类型并测试更多无效长度/ dbo.IsInt32('99999999999999999999999999999999999999999999930'), /* 18个字符，但显然不是有效的整数 */ dbo.IsInt32('999999999999999999'); - Orlando Colamatteo

我尝试提出了一个解决方案，应该可以解决所有边界情况：https://dev59.com/8nI-5IYBdhLWcg3wI0t9#24250511 - Douglas

同意这可能是一个问题，对于非常复杂、长的边缘情况。但对于基本整数（即1到1000之间的数字）测试非常有效。在这种情况下，您可以轻松地向字符串添加长度检查。 - Jerry

@Joshua - 在我的情况下，SELECT ISNUMERIC (CHAR(11) + '.e0') 返回 0，因此通过了测试，而不是失败。 - Reversed Engineer

Answer 2

在他的文章《我能将这个字符串转换为整数吗？》中，Itzik Ben-Gan提供了一个纯T-SQL的解决方案和一个使用CLR的解决方案。

你应该选择哪个解决方案？

是T-SQL还是CLR解决方案更好？ 使用T-SQL解决方案的优点在于，您不需要离开T-SQL编程领域。但是，CLR解决方案具有两个重要的优势：它更简单和更快。当我在拥有100万行的表上测试两种解决方案时，CLR解决方案仅需花费2秒，而不是7秒（用于T-SQL解决方案），在我的笔记本电脑上运行。因此，下一次您需要检查给定的字符串是否可以转换为整数时，您可以在本文中包含我提供的T-SQL或CLR解决方案。

如果您只想使用T-SQL，则使用纯T-SQL解决方案。如果性能比方便更重要，则使用CLR解决方案。

纯T-SQL解决方案比较棘手。它将内置的ISNUMERIC函数与模式匹配和转换相结合，以检查字符串是否表示整数。

SELECT keycol, string, ISNUMERIC(string) AS is_numeric,
  CASE
    WHEN ISNUMERIC(string) = 0     THEN 0
    WHEN string LIKE '%[^-+ 0-9]%' THEN 0
    WHEN CAST(string AS NUMERIC(38, 0))
      NOT BETWEEN -2147483648. AND 2147483647. THEN 0
    ELSE 1
  END AS is_int
FROM dbo.T1;

CLR解决方案中的T-SQL部分更简单。您调用fn_IsInt函数的方式与调用ISNUMERIC相同。

SELECT keycol, string, ISNUMERIC(string) AS is_numeric,
  dbo.fn_IsInt(string) AS is_int
FROM dbo.T1;

C#部分只是.NET解析函数Int32.TryParse的包装器。这是因为SQL Server int和.NET Int32都是32位有符号整数。

using System;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlBoolean fn_IsInt(SqlString s)
    {
        if (s.IsNull)
            return SqlBoolean.False;
        else
        {
            Int32 i = 0;
            return Int32.TryParse(s.Value, out i);
        }
    }
};

请阅读Itzik的文章，以获取这些代码示例的完整解释。

- Iain Samuel McLean Elder

---

如果输入的内容超过38个字符，纯T-SQL函数将会出现“数据截断”错误。请参考我的SQLFiddle，在那里我比较了这个解决方案和目前被接受的答案。 - Iain Samuel McLean Elder

ISNUMERIC(',') 返回1（真），但在我看来是假的！ - Marc

Answer 3

使用SQL Server 2005及更高版本，您可以在LIKE运算符中使用类似于正则表达式的字符类。请参见此处。

要检查一个字符串是否为非负整数（即十进制数字序列），您可以测试它是否不包含其他字符。

SELECT numstr
  FROM table
 WHERE numstr NOT LIKE '%[^0-9]%'

注意1：这也会返回空字符串。

注意2：使用 LIKE '%[0-9]％' 将返回任何包含至少一个数字的字符串。

请参见演示

- 1010

---

假设您只想返回正整数。 - DatumPoint

@DatumPoint 你说得对，它适用于正整数或0。 - 1010

Answer 4

WHERE IsNumeric(MY_FIELD) = 1 AND CAST(MY_FIELD as VARCHAR(5)) NOT LIKE '%.%'

这可能是最简单的解决方案。除非您的MY_FIELD包含 .00 或类似的内容。如果是这种情况，请将其转换为浮点数以删除任何尾随的 .00。

- KevinSwiecicki

---

这对逗号分隔的值无效。例如，ISNUMERIC（'123,456,789'）返回1。 - Elaskanator

Answer 5

召唤亡灵。
从SQL-Server 2012开始，您可以使用TRY_CAST。如果转换不成功，则返回NULL。

示例：

DECLARE @foo varchar(200)
SET @foo = '0123' 
-- SET @foo = '-0123' 
-- SET @foo = '+0123' 
-- SET @foo = '+-0123' 
-- SET @foo = '+-0123' 
-- SET @foo = '.123' 
-- SET @foo = '1.23' 
-- SET @foo = '.' 
-- SET @foo = '..' 
-- SET @foo = '0123e10' 

SELECT CASE WHEN TRY_CAST(@foo AS integer) IS NULL AND @foo IS NOT NULL THEN 0 ELSE 1 END AS isInteger 

这是唯一真正可靠的方法。

如果您需要支持SQL-Server 2008，则回退到Sam DeHaan的回答：

SELECT CASE WHEN ISNUMERIC(@foo + '.e0') = 1 THEN 1 ELSE 0 END AS isInteger 

SQL-Server 2012之前（又称2008R2）的支持将于2019年7月9日结束。此时很快，可能会停止对2012之前版本的支持。
在此时，我不再使用任何其他技巧。只需告诉您节俭的客户更新 - 自2008年以来已经过去了10多年。

- Stefan Steiger

Answer 6

使用TRY_CONVERT作为SQL中的替代方法，类似于.NET中的TryParse。IsNumeric()函数并不知道空字符串被视为(整数)零，并且有些完全有效的货币符号单独出现时并不会被转换为(货币)零。参考资料

SELECT @MY_VAR = CASE WHEN TRY_CONVERT(INT,MY_FIELD) IS NOT NULL THEN MY_FIELD
                 ELSE 0
                 END
FROM MY_TABLE
WHERE MY_OTHER_FIELD = 'MY_FILTER'

- Santosh Pujari

---

非常好的回答。我不知道为什么这个强有力的回答只得到了一个赞，但是我的是第二个。 - Ashok kumar

Answer 7

以下是WHERE子句的正确用法；如果要将函数包装起来，请使用CASE WHEN。

 ISNUMERIC(table.field) > 0 AND PATINDEX('%[^0123456789]%', table.field) = 0

- Joshua

Answer 8

请看下面的查询是否有所帮助。

SELECT *
FROM MY_TABLE
WHERE CHARINDEX('.',MY_FIELD) = 0 AND CHARINDEX(',',MY_FIELD) = 0       
AND ISNUMERIC(MY_FIELD) = 1 AND CONVERT(FLOAT,MY_FIELD) / 2147483647 <= 1

- Gopakumar N.Kurup

Answer 9

以下是与 IsNumeric 函数相关的解决方案：

select * from A where ISNUMERIC(x) =1 and X not like '%.%'

或者使用：

select * from A where x **not like** '%[^0-9]%'

请注意，这些查询语句将使结果仅包含数字而不包括小数点。

- Subhash

Answer 10

声明 @i numeric(28,5) = 12.0001
如果 (@i/cast(@i as int) > 1)
开始
    选择 '这不是整数'
结束
否则
开始
    选择 '这是整数'
结束

- Saravanan A

---

这是我使用的解决方案。它还有一个额外的好处，就是它是整数的教科书定义。我会将这个标记为回答OP问题的答案。 - AcePL