Remove decimal values using SQL query

Question

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

User · Answer

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.

User · Answer

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

User · Answer

Here column name must be decimal   select CAST columnname AS decimal 38 0   from table

User · Answer

Since all your values end with   00   there will be no rounding issues  this will work  SELECT CAST columnname AS INT  AS columnname from tablename   to update  UPDATE tablename SET columnname   CAST columnname AS INT  WHERE

User · Answer

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 gt Options gt Designers gt Table and database designer gt 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

User · Answer

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 -   Either cast this column as int in Select Clause 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

User · Answer

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

[sql] Remove decimal values using SQL query

Examples related to sql

Examples related to sql-server