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.
I want values like :
12 15 18 20
My replace query :
select height(replace (12.00, '')) from table;
Your data type is DECIMAL with decimal places, say 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 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.
If you only want integers in your column, then change its data type to INT. It makes no sense to use DECIMAL then.
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.)
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.
If it's a decimal data type and you know it will never contain decimal places you can consider setting the scale property 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").
Othewise, you of course remove it using SQL query:
select replace(cast([height] as varchar), '.00', '') from table
As I understand your question, You have one table with column as datatype decimal(18,9). And the column contains the data as follows:-
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:-
To apply, First very simple just use the cast in select clause
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
and to do that
ALTER TABLE tablename ALTER COLUMN columnname decimal(9,0)
Then execute this
UPDATE tablename SET count = CAST(columnname AS INT)
Here column name must be decimal.
select CAST(columnname AS decimal(38,0)) from table
Simply update with a convert/cast to INT:
UPDATE YOUR_TABLE SET YOUR_COLUMN = CAST(YOUR_COLUMN AS INT) WHERE -- some condition is met if required
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
First of all, you tried to replace the entire 12.00 with '', which isn't going to give your desired results.
Second you are trying to do replace directly on a decimal. Replace must be performed on a string, so you have to 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','')
This script works:
DECLARE @Height decimal(6,2); SET @Height = 12.00; SELECT @Height, REPLACE(CAST(@Height AS varchar(31)),'.00','');