SQL Server 中 IsInteger 的最佳等效项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2358147/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best equivalent for IsInteger in SQL Server
提问by Mayo
What is the best way to determine whether or not a field's value is an integer in SQL Server (2000/2005/2008)?
在 SQL Server (2000/2005/2008) 中确定字段值是否为整数的最佳方法是什么？
IsNumeric returns true for a variety of formats that would not likely convert to an integer. Examples include '15,000' and '15.1'.
对于不可能转换为整数的各种格式，IsNumeric 返回 true。示例包括“15,000”和“15.1”。
You can use a like statement but that only appears to work well for fields that have a pre-determined number of digits...
您可以使用 like 语句，但这似乎只适用于具有预定位数的字段...
select * where zipcode like '[0-9][0-9][0-9][0-9][0-9]'
I could write a user defined function that attempts to convert a varchar parameter to an int within a try/catch block but I'm checking with the community to see if someone has come across any succient methods to achieve this goal - preferably one that can be used within the where clause of a SQL statement without creating other objects.
我可以编写一个用户定义的函数，尝试在 try/catch 块中将 varchar 参数转换为 int，但我正在与社区一起检查是否有人遇到过任何简洁的方法来实现此目标 - 最好是可以在 SQL 语句的 where 子句中使用，而不创建其他对象。
回答by gbn
回答by AdaTheDev
1 approach is
1 方法是
zipcode NOT LIKE '%[^0-9]%'
Double negatives, got to love 'em!
双重否定，必须爱他们！
回答by OMG Ponies
If SQL Server 2005+, I'd enable CLR and create the function to support regexes. For SQL Server 2000, see this article for creating a UDF to do the same thing.
如果 SQL Server 2005+，我会启用 CLR 并创建函数来支持 regexes。对于 SQL Server 2000，请参阅本文以创建 UDF 以执行相同的操作。
Then I'd use the regex: ^\d{5}$
然后我会使用正则表达式： ^\d{5}$
回答by george
This expression gives 1 for an integer value and 0 otherwise
此表达式为整数值提供 1，否则为 0
floor((floor(abs(zipcode)))/abs(zipcode))
回答by cjbarth
Why not just use the following? I can't see to find any cases where it fails.
为什么不使用以下内容？我看不到任何失败的情况。
- 1 = integer
- 0 = not integer
- null = non-numeric
- 1 = 整数
- 0 = 不是整数
- null = 非数字
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
回答by jessieloo
After moving to sql 2008, I was struggling with isnumeric('\8') returning true but throwing an error when casting to an integer. Apparently forward slash is valid currency for yen or won - (reference http://www.louiebao.net/blog/200910/isnumeric/)
转移到 sql 2008 后，我一直在努力解决 isnumeric('\8') 返回 true 但在转换为整数时抛出错误的问题。显然正斜杠是日元或韩元的有效货币 - （参考http://www.louiebao.net/blog/200910/isnumeric/）
My solution was
我的解决方案是
case when ISNUMERIC(@str) > 0 and not rtrim(@str) LIKE '[^0-9]%' and not rtrim(@str) LIKE '%[^0-9]' and not rtrim(@str) LIKE '[^0-9]%' then rtrim(@str) else null end
回答by Gopakumar N.Kurup
See whether the below code will help. In the below values only 9, 2147483647, 1234567 are eligible as Integer. We can create this as function and can use this.
看看下面的代码是否会有所帮助。在下面的值中，只有 9、2147483647、1234567 有资格作为整数。我们可以将它创建为函数并可以使用它。
CREATE TABLE MY_TABLE(MY_FIELD VARCHAR(50))
INSERT INTO MY_TABLE
VALUES('9.123'),('1234567'),('9'),('2147483647'),('2147483647.01'),('2147483648'), ('2147483648ABCD'),('214,7483,648')
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
DROP TABLE MY_TABLE
回答by Todd181
I did it using a Case statement: Cast(Case When Quantity/[# of Days]= Cast(Quantity/[# of Days] as int) Then abs(Quantity/[# of Days]) Else 0 End as int)
我使用 Case 语句做到了： Cast(Case When Quantity/[# of Days]= Cast(Quantity/[# of Days] as int) Then abs(Quantity/[# of Days]) Else 0 End as int)
回答by vibs2006
To test whether the input value is an integer or not we can use SQL_VARIANT_PROPERTYfunction of SQL SERVER.
要测试输入值是否为整数，我们可以使用SQL SERVER 的SQL_VARIANT_PROPERTY函数。
The following SQL Script will take input and test it whether the data type turns out to be integer or not
以下 SQL 脚本将接受输入并测试数据类型是否为整数
declare @convertedTempValue bigint, @inputValue nvarchar(255) = '1' --Change '1' to any input value
set @convertedTempValue = TRY_PARSE(@inputValue as bigint) --we trying to convert to bigint
declare @var3 nvarchar(255) = cast (SQL_VARIANT_PROPERTY(@convertedTempValue,'BaseType') as nvarchar(255)) --we using SQL_VARIANT_PROPERTY to find out datatype
if ( @var3 like '%int%')
begin
print 'value is integer'
end
else
begin
print 'value is non integer'
end
go
回答by MikeTeeVee
I came up with the perfect answer for this on another StackO question.
It also proves you cannotuse ".0e0" like one user suggests here.
It does so without CLR or non-scalar functions.
Please check it out: https://stackoverflow.com/a/10645764/555798
我在另一个 StackO 问题上想出了一个完美的答案。
它还证明您不能像一位用户在这里建议的那样使用“.0e0”。
它不需要 CLR 或非标量函数。
请查看：https: //stackoverflow.com/a/10645764/555798
