SQL Server中如何判断数字是浮点数还是整数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7752722/
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
How to determine whether the number is float or integer in SQL Server?
提问by Azeem
I need to write this query in sql server:
我需要在 sql server 中编写这个查询：
IF isFloat(@value) = 1
BEGIN
PRINT 'this is float number'
END
ELSE
BEGIN
PRINT 'this is integer number'
END
Please help me out with this, thanks.
请帮我解决这个问题，谢谢。
回答by Martin Smith
declare @value float = 1
IF FLOOR(@value) <> CEILING(@value)
BEGIN
PRINT 'this is float number'
END
ELSE
BEGIN
PRINT 'this is integer number'
END
回答by Nico van Niekerk
Martin, under certain circumstances your solution gives an incorrect result if you encounter a value of 1234.0, for example. Your code determines that 1234.0 is an integer, which is incorrect.
Martin，在某些情况下，例如，如果您遇到值 1234.0，您的解决方案会给出不正确的结果。您的代码确定 1234.0 是一个整数，这是不正确的。
This is a more accurate snippet:
这是一个更准确的片段：
if cast(cast(123456.0 as integer) as varchar(255)) <> cast(123456.0 as varchar(255))
begin
print 'non integer'
end
else
begin
print 'integer'
end
Regards,
问候，
Nico
妮可
回答by Saradhi
DECLARE @value FLOAT = 1.50
IF CONVERT(int, @value) - @value <> 0
BEGIN
PRINT 'this is float number'
END
ELSE
BEGIN
PRINT 'this is integer number'
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
OR
或者
DECLARE @num VARCHAR(100)
SET @num = '2147483648AS'
IF ISNUMERIC(@num) = 1 AND @num NOT LIKE '%.%' AND @num NOT LIKE '%,%'
BEGIN
IF CONVERT(FLOAT,@num) / 2147483647 <= 1
PRINT 'INTEGER'
ELSE
PRINT 'NOT-INTEGER'
END
ELSE
PRINT 'NOT-INTEGER'
