T-sql——确定值是否为整数

I want to determine if a value is integer (like TryParse in .NET). Unfortunatelly ISNUMERIC does not fit me because I want to parse only integers and not every kind of number. Is there such thing as ISINT or something?

我想确定一个值是否是整数(比如。net中的TryParse)。Unfortunatelly ISNUMERIC不适合我，因为我只想解析整数，而不是所有类型的数字。有ISINT之类的东西吗?

Here is some code to make things clear. If MY_FIELD is not int, this code would fail:

这里有一些代码可以让事情变得清晰。如果MY_FIELD不是int，则此代码将失败:

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

Thank you

谢谢你！

12 个解决方案

#1

Here's a blog post describing the creation of an IsInteger UDF.

这是一篇描述创建IsInteger UDF的博客文章。

Basically, it recommends adding '.e0' to the value and using IsNumeric. In this way, anything that already had a decimal point now has two decimal points, causing IsNumeric to be false, and anything already expressed in scientific notation is invalidated by the e0.

基本上，它建议增加“。e0'的值和使用是数值。这样，任何已经有十进制点的东西现在都有两个十进制点，导致IsNumeric为false，并且任何已经用科学符号表示的东西都被e0作废。

#2

In his article Can I convert this string to an integer?, Itzik Ben-Gan provides a solution in pure T-SQL and another that uses the CLR.

在他的文章中，我可以把这个字符串转换成一个整数吗?， Itzik Ben-Gan提供了纯T-SQL和另一个使用CLR的解决方案。

Which solution should you choose?

你应该选择哪种解决方案?

Is the T-SQL or CLR Solution Better? The advantage of using the T-SQL solution is that you don’t need to go outside the domain of T-SQL programming. However, the CLR solution has two important advantages: It's simpler and faster. When I tested both solutions against a table that had 1,000,000 rows, the CLR solution took two seconds, rather than seven seconds (for the T-SQL solution), to run on my laptop. So the next time you need to check whether a given string can be converted to an integer, you can include the T-SQL or CLR solution that I provided in this article.

T-SQL或CLR解决方案更好吗?使用T-SQL解决方案的好处是，您不需要超出T-SQL编程的范围。然而，CLR解决方案有两个重要的优点:它更简单和更快。当我对一个有1,000,000行的表测试这两个解决方案时，CLR解决方案在我的笔记本上运行需要2秒，而不是7秒(对于T-SQL解决方案)。因此，下次您需要检查给定字符串是否可以转换为整数时，您可以包括我在本文中提供的T-SQL或CLR解决方案。

If you only want to maintain T-SQL, then use the pure T-SQL solution. If performance is more important than convenience, then use the CLR solution.

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

The pure T-SQL Solution is tricky. It combines the built-in ISNUMERIC function with pattern-matching and casting to check if the string represents an int.

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

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;

The T-SQL part of the CLR solution is simpler. You call the fn_IsInt function just like you would call ISNUMERIC.

CLR解决方案的T-SQL部分更简单。调用fn_IsInt函数，就像调用ISNUMERIC一样。

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

The C# part is simply a wrapper for the .NET's parsing function Int32.TryParse. This works because the SQL Server int and the .NET Int32 are both 32-bit signed integers.

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);
        }
    }
};

Please read Itzik's article for a full explanation of these code samples.

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

#3

With sqlserver 2005 and later you can use regex-like character classes with LIKE operator. See here.

有了sqlserver 2005，之后您就可以使用类似regex的字符类了，就像操作符一样。在这里看到的。

To check if a string is a non-negative integer (it is a sequence of decimal digits) you can test that it doesn't contain other characters.

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

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

Note1: This will return empty strings too.

注1:这也将返回空字符串。

Note2: Using LIKE '%[0-9]%' will return any string that contains at least a digit.

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

See fiddle

看到小提琴

#4

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

That is probably the simplest solution. Unless your MY_FIELD contains .00 or something of that sort. In which case, cast it to a float to remove any trailing .00s

这可能是最简单的解决方案。除非你的MY_FIELD包含。00或者类似的东西。在这种情况下，把它扔到一个浮子上，以除去任何后面的。

#5

See whether the below query will help

看看下面的查询是否有用

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

#6

This work around with IsNumeric function will work:

这个关于IsNumeric函数的工作可以:

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

从where ISNUMERIC(x) =1中选择*，而不像'%.%'

or Use

或使用

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

select *从x不像' %[^ 0 - 9]% '

#7

The following is correct for a WHERE clause; to make a function wrap it in CASE WHEN.

以下是WHERE子句的正确用法;要使一个函数包装它，以防什么时候。

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

#8

I think that there is something wrong with your database design. I think it is a really bad idea to mix varchar and numbers in one column? What is the reason for that?

我认为您的数据库设计有问题。我认为把varchar和数字混在一列是一个很糟糕的主意?原因是什么?

Of course you can check if there are any chars other than [0-9], but imagine you have a 1m rows in table and your are checking every row. I think it won't work well.

当然，您可以检查除了[0-9]之外是否有任何chars，但是假设您有一个1m的表，并且您正在检查每一行。我认为这行不通。

Anyway if you really want to do it I suggest doing it on the client side.

不管怎样，如果你真的想做，我建议你在客户端做。

#9

declare @i numeric(28,5) = 12.0001


if (@i/cast(@i as int) > 1)
begin
    select 'this is not int'
end
else
begin
    select 'this is int'
end

#10

Sometimes you don't get to design the database, you just have to work with what you are given. In my case it's a database located on a computer that I only have read access to which has been around since 2008.

有时候你不需要设计数据库，你只需要用你得到的东西。在我的情况下，它是一个数据库，位于一台计算机上，我只有读访问权限，从2008年开始就有了。

I need to select from a column in a poorly designed database which is a varchar with numbers 1-100 but sometimes a random string. I used the following to get around it (although I wish I could have re designed the entire database).

我需要从设计糟糕的数据库中选择一列，该数据库是一个varchar，编号为1-100，但有时是一个随机字符串。我使用下面的方法来解决这个问题(尽管我希望能够重新设计整个数据库)。

SELECT A from TABLE where isnumeric(A)=1

#11

I have a feeling doing it this way is the work of satan, but as an alternative:

我有一种感觉，这样做是撒旦的杰作，但作为另一种选择:

How about a TRY - CATCH?

试试抓鱼怎么样?

DECLARE @Converted as INT
DECLARE @IsNumeric BIT

BEGIN TRY
    SET @Converted = cast(@ValueToCheck as int)
    SET @IsNumeric=1
END TRY
BEGIN CATCH
    SET @IsNumeric=0
END CATCH

select IIF(@IsNumeric=1,'Integer','Not integer') as IsInteger

This works, though only in SQL Server 2008 and up.

这只适用于SQL Server 2008和以上版本。

#12

-2

Why not just do something like:

为什么不这样做呢:

CASE
WHEN ROUND(MY_FIELD,0)=MY_FIELD THEN CAST(MY_FIELD AS INT)
ELSE MY_FIELD
END
as MY_FIELD2

#1