在SQL中省略以小数结尾的数字 [英] Omit numbers that ends with decimal in SQL

问题描述

我想省略所有以小数字结尾的数字，例如145.和145.5在SQL Server中。



我尝试过：



声明@value float = 1

选择'abc'

其中@value不喜欢％（ CAST（@value AS INT）+'。'）％

I want to omit all the numbers that ends with decimal like 145. and 145.5 in SQL Server.

What I have tried:

declare @value float = 1
select 'abc'
where @value not like % (CAST(@value AS INT) + '.') %

推荐答案

嗯...浮点值不是以小数结尾 - 它们是当作为字符串呈现给用户时，可能包含或不包含小数点的数值。因此，它们都可能包含十进制：100同时也是100.0，100.000和1.0E2。

因为示例代码中的@value是FLOAT，所以尝试将它转换为int然后添加一个字符串只会产生错误，因为float会尝试将其余的数据转换回数字 - 而'％'不是数字！更糟糕的是，LIKE不能使用数字，只能使用字符串，所以如果你在某些时候将它转换回字符串就不会起作用。

如果你试图消除那些值是xxx.0和xxx.5，然后这并不复杂：

Um...floating point values don't "end in a decimal" - they are a numeric value that may or may not contain a decimal point when presented to the user as a string. As a result they all potentially contain "a decimal": 100 is also 100.0, and 100.000, and 1.0E2 all at the same time.
And since @value in your example code is a FLOAT, trying to cats it to an int and then add a string will just produce an error, since the float will try to cast the rest of the data back to a numeric - and '%' isn't a number!. To make matters worse, LIKE doesn't work with numbers, only strings so it wouldn't work if you did convert it back to a string at some point.
If you are trying to eliminate values that are xxx.0 and xxx.5, then that's not complex:

declare @value float = 1
select 'abc'
where (@value -CAST (@value AS INT) != 0) AND (@value -CAST (@value AS INT) !=.5)

参见 Modulo（Transact-SQL） [ ^ ] - 其余为0

See Modulo (Transact-SQL)[^] - where remainder is 0

我想你只想选择整数值。

- 使用楼层

I guess you want to select only integer values.
- use floor

WHERE @value = floor(@value)

- 使用modulo

- use modulo

WHERE @value % 1 = 0 

- 使用回合

- use round

WHERE @value = round(@value, 0) 

这篇关于在SQL中省略以小数结尾的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！