使用 SQL 查询删除十进制值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27822556/
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
Remove decimal values using SQL query
提问by rahul bhatt
I am having following values in database table :
我在数据库表中有以下值：
12.00
15.00
18.00
20.00
I want to remove all decimal ZEROS from all values , So how can I do this using SQL query. I tried replace query but that is not working.
我想从所有值中删除所有十进制零，那么我该如何使用 SQL 查询来做到这一点。我尝试替换查询，但这不起作用。
I want values like :
我想要这样的价值观：
12
15
18
20
My replace query :
我的替换查询：
select height(replace (12.00, '')) from table;
Please help.
请帮忙。
回答by Dbloch
Since all your values end with ".00", there will be no rounding issues, this will work
由于您的所有值都以“.00”结尾，因此不会有舍入问题，这将起作用
SELECT CAST(columnname AS INT) AS columnname from tablename
to update
更新
UPDATE tablename
SET columnname = CAST(columnname AS INT)
WHERE .....
回答by Bhavin
Here column name must be decimal.
这里列名必须是十进制。
select CAST(columnname AS decimal(38,0)) from table
回答by Tanner
Simply update with a convert/cast to INT:
只需使用转换/转换为 INT 进行更新：
UPDATE YOUR_TABLE
SET YOUR_COLUMN = CAST(YOUR_COLUMN AS INT)
WHERE -- some condition is met if required
Or convert:
或转换：
UPDATE YOUR_TABLE
SET YOUR_COLUMN = CONVERT(INT, YOUR_COLUMN)
WHERE -- some condition is met if required
To test you can do this:
要进行测试，您可以执行以下操作：
SELECT YOUR_COLUMN AS CurrentValue,
CAST(YOUR_COLUMN AS INT) AS NewValue
FROM YOUR_TABLE
回答by Tab Alleman
First of all, you tried to replace the entire 12.00 with '', which isn't going to give your desired results.
首先，您尝试用 '' 替换整个 12.00，这不会给出您想要的结果。
Second you are trying to do replace directly on a decimal. Replace must be performed on a string, so you have to CAST.
其次，您试图直接在小数上进行替换。必须对字符串执行替换，因此您必须进行 CAST。
There are many ways to get your desired results, but this replace would have worked (assuming your column name is "height":
有很多方法可以得到你想要的结果，但是这个替换会起作用（假设你的列名是“高度”：
REPLACE(CAST(height as varchar(31)),'.00','')
EDIT:
编辑：
This script works:
此脚本有效：
DECLARE @Height decimal(6,2);
SET @Height = 12.00;
SELECT @Height, REPLACE(CAST(@Height AS varchar(31)),'.00','');
回答by Ivan Sivak
If it's a decimal data type and you know it will never contain decimal places you can consider setting the scaleproperty to 0. For example to decimal(18, 0). This will save you from replacing the ".00"characters and the query will be faster. In such case, don't forget to to check if the "prevent saving option" is disabled (SSMS menu "Tools>Options>Designers>Table and database designer>prevent saving changes that require table re-creation").
如果它是十进制数据类型并且您知道它永远不会包含小数位，您可以考虑将scale属性设置为 0。例如 to decimal(18, 0)。这将使您免于替换".00"字符并且查询会更快。在这种情况下，不要忘记检查“防止保存选项”是否被禁用 ( SSMS menu "Tools>Options>Designers>Table and database designer>prevent saving changes that require table re-creation")。
Othewise, you of course remove it using SQL query:
Othewise，您当然可以使用 SQL 查询删除它：
select replace(cast([height] as varchar), '.00', '') from table
回答by HaveNoDisplayName
As I understand your question, You have one table with column as datatype decimal(18,9). And the column contains the data as follows:-
据我了解您的问题，您有一张表，其中列的数据类型为decimal(18,9)。该列包含如下数据：-
12.00
15.00
18.00
20.00
Now if you want to show record on UI without decimal value means like (12,15,18,20) then there are two options:-
现在，如果您想在 UI 上显示没有十进制值的记录，例如 (12,15,18,20) 那么有两个选项：-
- Either cast this column as int in Select Clause
- or may be you want to update this column value like (12,15,18,20).
- 在选择子句中将此列转换为 int
- 或者您可能想要更新此列值，例如 (12,15,18,20)。
To apply, First very simple just use the cast in select clause
要申请，首先非常简单，只需在 select 子句中使用强制转换
select CAST(count AS INT) from tablename;
But if you want to update your column data with int value then you have to update you column datatype
但是如果你想用 int 值更新你的列数据，那么你必须更新你的列数据类型
and to do that
并做到这一点
ALTER TABLE tablename ALTER COLUMN columnname decimal(9,0)
Then execute this
然后执行这个
UPDATE tablename
SET count = CAST(columnname AS INT)
回答by Thorsten Kettner
Your data type is DECIMAL with decimal places, say DECIMAL(10,2). The values in your database are 12, 15, 18, and 20.
您的数据类型是带小数位的 DECIMAL，例如 DECIMAL(10,2)。数据库中的值为 12、15、18 和 20。
12 is the same as 12.0 and 12.00 and 12.000 . It is up to the tool you are using to select the data with, how to display the numbers. Yours either defaults to two digits for decimals or it takes the places from your data definition.
12 与 12.0 和 12.00 和 12.000 相同。这取决于您用来选择数据的工具，以及如何显示数字。您的小数点要么默认为两位数，要么从您的数据定义中获取位置。
If you only want integers in your column, then change its data type to INT. It makes no sense to use DECIMAL then.
如果您只想在列中使用整数，请将其数据类型更改为 INT。那么使用 DECIMAL 是没有意义的。
If you want integers and decimals in that column then stay with the DECIMAL type. If you don't like the way you are shown the values, then format them in your application. It's up to that client program to decide for instance if to display point or comma for the decimal separator. (The database can be used from different locations.)
如果您想要该列中的整数和小数，请使用 DECIMAL 类型。如果您不喜欢显示值的方式，请在您的应用程序中设置它们的格式。例如，由客户端程序决定是否显示小数点分隔符的点或逗号。（数据库可以从不同的位置使用。）
Also don't rely on any database or session settings like a decimal separator being a point and not a comma and then use REPLACE on it. That can work for one person and not for the other.
也不要依赖任何数据库或会话设置，比如小数点分隔符是一个点而不是逗号，然后在它上面使用 REPLACE。这对一个人有效，对另一个人无效。
