[sql] Remove decimal values using SQL query

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;

Please help.

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:-

1. Either cast this column as int in Select Clause
2. or may be you want to update this column value like (12,15,18,20).

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

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

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

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

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','')

EDIT:

This script works:

DECLARE @Height decimal(6,2);
SET @Height = 12.00;
SELECT @Height, REPLACE(CAST(@Height AS varchar(31)),'.00','');