0

How would I delete all the decimals from a column in SQL Server 2008?

If I have a column X_Coord and had three rows with the value, how would I trim it so that there are NO decimals after the last whole number?

For example, let's say my table is called RCMP, and the column is below:

X_Coord
---------------
- 5588790.77000
- 5588873.79000
- 5588943.71000

How would I remove the decimals in a single query?

I tried ROUND, but that ends up making the values appear as ie 5588790.00000.

I want it to appear as: 5588790.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
pistolpunz
  • 5
  • 2
  • 6

1 Answers1

0

Cast the decimal data type to an integer.

SELECT CAST(x_coord AS INT)
FROM dbo.RMCP

Edit: I have updated the code to reflect how to change the data type. This is a big impact change so be very careful. I would urge you to test this in development.

if object_id('#Demo') is not null
drop table #Demo;
go

create table #Demo(x_coord decimal(12,5))
insert into #Demo values(5588790.77000),(5588873.79000),(5588943.71000)

alter table #Demo
ALTER COLUMN x_coord INT NULL

select *
from #Demo
GO

--or this works

if object_id('#Demo') is not null
drop table #Demo;
go

create table #Demo(x_coord decimal(12,5))
insert into #Demo values(5588790.77000),(5588873.79000),(5588943.71000)

alter table #Demo
add new_x_coord INT NULL

UPDATE #Demo SET new_x_coord = CAST(x_coord AS INT)
GO

--**********************  dont drop anything until you confirm the data is good!!!!!!!!!!!!! and test this in development *************************************
ALTER TABLE #Demo
DROP COLUMN x_coord

exec sp_rename 'dbo.#Demo.new_x_coord','x_coord','COLUMN'

select *
from #Demo
GO
Adam Haines
  • 900
  • 5
  • 7
  • thanks, but this just selects it. I need to convert the column. – pistolpunz Jul 26 '13 at 15:21
  • You will need to change the data type of the column to int or add a new column and execute an update statement with the cast above to load the new column. Then you can drop the old column. – Adam Haines Jul 26 '13 at 15:32