SQL Server，字段是int类型的where语句？

Question

我该如何实现：

select * from table where column_value is int

我知道我可以通过内部连接系统表和类型表来解决问题，但我想知道是否有更加优雅的方法。

请注意，column_value是一个varchar类型的字段，它“可能”有一个int值，但不一定。

也许我可以将其强制转换并捕获错误？但这似乎是一种曲线救国的方法。

- Scott Klarenbach

---

说实话，这里的技巧是你有一个文本列中的数据想要作为数字处理。你真的不应该这样做，但我猜你已经发现了为什么。 - Lasse V. Karlsen

Answer 1

select * from table
where column_value not like '[^0-9]'

如果允许负整数，则需要类似以下的内容：

where column_value like '[+-]%' 
and substring(column_value,patindex('[+-]',substring(column_value,1))+1,len(column_value))
not like '[^0-9]'

如果column_value可能是超出“int”类型限制的整数，并且您希望排除这些情况，则需要更多的代码。

- Steve Kass

---

不确定这是否正确，但那样只会测试第一个字符是否为“非数字”，难道不应该使用：WHERE column_value not like '%[^0-9]%'？ - VoidKing

OR column_value like '%[0-9]%'？ - VoidKing

Answer 2

如果你想要实现自定义函数：

CREATE Function dbo.IsInteger(@Value VARCHAR(18))
RETURNS BIT
AS 
BEGIN    
     RETURN ISNULL(     
         (SELECT    CASE WHEN CHARINDEX('.', @Value) > 0 THEN 
                            CASE WHEN CONVERT(int, PARSENAME(@Value, 1)) <> 0  THEN 0  ELSE 1 END  
                    ELSE 1 
                    END      
          WHERE     ISNUMERIC(@Value + 'e0') = 1), 0)

END

当输入表达式可以转换为有效的整数、浮点数、货币或十进制类型时，ISNUMERIC返回1；否则返回0。返回值为1保证该表达式可以转换为这些数字类型之一。

- Svetlozar Angelov

---

它将'123.'检测为整数，但我们无法转换它 :( - Faiz

Answer 3

我会像Svetlozar Angelov建议的那样做一个UDF，但我会先检查ISNUMERIC（如果不是，则返回0），然后检查column_value % 1 = 0以查看它是否为整数。

这里是主体可能看起来像什么。您必须将模数逻辑放在单独的分支中，因为如果值不是数字，它将引发异常。

DECLARE @RV BIT
IF ISNUMERIC(@value) BEGIN
    IF CAST(@value AS NUMERIC) % 1 = 0 SET @RV = 1
    ELSE SET @RV = 0
END
ELSE SET @RV = 0
RETURN @RV

- John M Gant

Answer 4

这应该处理所有情况而不抛出任何异常：

--This handles dollar-signs, commas, decimal-points, and values too big or small,
--  all while safely returning an int.
DECLARE @IntString as VarChar(50) = '$1,000.'
SELECT CAST((CASE WHEN --This IsNumeric check here does most of the heavy lifting.  The rest is Integer-Specific
                       ISNUMERIC(@IntString) = 1
                       --Only allow Int-related characters.  This will exclude things like 'e' and other foreign currency characters.
                   AND @IntString NOT LIKE '%[^ $,.\-+0-9]%' ESCAPE '\'--'
                       --Checks that the value is not out of bounds for an Integer.
                   AND CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(38)) BETWEEN -2147483648 AND 2147483647
                       --This allows values with decimal-points for count as an Int, so long as there it is not a fractional value.
                   AND CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(38)) = CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(38,2))
                       --This will safely convert values with decimal points to casting later as an Int.
                  THEN CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10))
             END) as Int)[Integer]

将此代码放入标量UDF中并调用ReturnInt()。
如果返回值为NULL，则它不是int（因此您需要IsInteger()）

如果您不喜欢键入“WHERE ReturnInt(SomeValue) IS NOT NULL”，则可以将其放入另一个标量UDF中，称为IsInt()，以调用此函数并简单地返回“ReturnInt(SomeValue) IS NOT NULL”。

很酷的是，UDF可以通过返回“安全”转换的int值来发挥双倍作用。
仅仅因为某些东西可能是int，并不意味着将其强制转换为int不会引发巨大的异常。这个函数可以为您解决这个问题。

此外，我建议避免其他解决方案，因为这种通用方法将处理逗号、小数点、美元符号，并检查可接受的Int值范围，而其他解决方案则不会-或者它们需要多个SET操作，这会阻止您使用Scalar-Function的逻辑以实现最大性能。

请参见下面的示例，并针对我的代码和其他代码进行测试：

--Proves that appending "e0" or ".0e0" is NOT a good idea.
select ISNUMERIC('$1' + 'e0')--Returns: 0.
select ISNUMERIC('1,000' + 'e0')--Returns: 0.
select ISNUMERIC('1.0' + '.0e0')--Returns: 0.

--While these are numeric, they WILL break your code
--   if you try to cast them directly as int.
select ISNUMERIC('1,000')--Returns: 1.
select CAST('1,000' as Int)--Will throw exception.
select ISNUMERIC('$1')--Returns: 1.
select CAST('$1' as Int)--Will throw exception.
select ISNUMERIC('10.0')--Returns: 1.
select CAST('10.0' as Int)--Will throw exception.
select ISNUMERIC('9999999999223372036854775807')--Returns: 1.  This is why I use Decimal(38) as Decimal defaults to Decimal(18).
select CAST('9999999999223372036854775807' as Int)--Will throw exception.

更新：
我在这里看到有人想要将类似于“123.”的值解析为整数。我已经更新了我的代码来处理这个问题。

注意：这会将“1.0”转换，但对于“1.9”则返回null。
如果您想要允许四舍五入，则可以调整“THEN”子句中的逻辑，添加Round()，如下所示：
ROUND(CAST(REPLACE(REPLACE(@IntString,'$',''),',','') as Decimal(10)), 0)
您必须还要删除检查“小数点”的“AND”，以允许四舍五入或截断。

- MikeTeeVee

Answer 5

为什么不使用以下内容并测试1？

DECLARE @TestValue nvarchar(MAX)
SET @TestValue = '1.04343234e5'

SELECT CASE WHEN ISNUMERIC(@TestValue) = 1
        THEN CASE WHEN ROUND(@TestValue,0,1) = @TestValue
            THEN 1
            ELSE 0
            END
        ELSE null
        END AS Analysis

- cjbarth

Answer 6

如果你只是想验证一个字符串是否全是数字而不仅仅是可转换为INT，你可以做这个非常糟糕的事情：

select LEN(
 REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
 '-1.223344556677889900e-1'
 ,'0','') ,'1','') ,'2','') ,'3','') ,'4','') ,'5','') ,'6','') ,'7','') ,'8','') ,'9','')
)

当字符串为空或仅包含数字时，它返回0。

为了将其作为“穷人版”整数的有用检查，您需要处理空字符串和初始负号。并手动确保它不会太长以适应您的INTEGER类型。

- Mark