Removing Decimal Values Using SQL Query
I have the following values in my 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 it with a SQL query. I tried to replace the request but it doesn't work.
I need values:
12 15 18 20
My replacement request:
select height(replace (12.00, '')) from table;
Please, help.
source to share
Just update with convert / cast to 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
source to share
First of all, you tried to replace all 12.00 with "', which will not give the desired results.
Second, you are trying to replace directly with a decimal. Replace has to be done on line, so you need CAST.
There are many ways to get the results you want, but this replacement would work (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','');
source to share
If this is the decimal data type, and you know that he will never have a decimal place, you may want to consider setting the property scale equal to 0. For example, decimal(18, 0)
. This will save you the hassle of replacing characters ".00"
and the query will be faster. In this case, do not forget 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"
).
Otherwise, you will of course remove it with an SQL query:
select replace(cast([height] as varchar), '.00', '') from table
source to share
As I understand your question, you have one table with a column as datatype decimal (18,9). And the column contains the following data: -
12.00
15.00
18.00
20.00
Now if you want to show the entry in the UI without the decimal value then like (12,15,18,20) then there are two options: -
- Either enter this column as int in Select Clause
- or perhaps you want to update that column value like (12,15,18,20).
To apply it is very easy at first to use a cast in the select clause
select CAST(count AS INT) from tablename;
But if you want to update the column data with int value, you need to update the column data type
and do it
ALTER TABLE tablename ALTER COLUMN columnname decimal(9,0)
Then do this
UPDATE tablename
SET count = CAST(columnname AS INT)
source to share
DECIMAL data type with decimal places, such as DECIMAL (10,2). The values in your database are 12, 15, 18, and 20.
12 is the same as 12.0 and 12.00 and 12,000. It depends on which tool you use to select the data, how the numbers are displayed. Yours either has two digits for decimal places by default, or takes space from your data definition.
If you only want integers in a column, change its data type to INT. Then there is no point in using DECIMAL.
If you want integers and decimals in this column, then stay with the DECIMAL type. If you don't like the way you display the values, then format them in your application. It is for this client program to decide, for example, to display a dot or comma for the decimal separator. (The database can be used from various places.)
Also, don't rely on any database or session settings such as the decimal separator being a point, not a comma, and then use REPLACE. This may work for one person and not another.
source to share