• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 20
  • Last Modified:

converting varChar with decimal places in to int

Hi, I'm having trouble converting a table that currently has data stored in a varchar format to int.

It works ok with most numbers but doesn't seem to work when there is a decimal point in them.

604152.7 fails
604152 works

Can someone please advise my best way round this please. I'm using SQL 2012 if that makes a difference and need these in int format for use in another program.

Thanks, Neil
0
Neil Thompson
Asked:
Neil Thompson
  • 5
  • 3
  • 2
  • +1
1 Solution
 
NorieVBA ExpertCommented:
Neil

What would you want to happen when there is a decimal point?
0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Apologies, I've just asked

We only need the 6 numbers before the .
0
 
Bill PrewCommented:
Try:

CAST(ROUND(ColName,0,1) AS INT)


»bp
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
That's great many thanks, how would I now go about updating all these in the table to remove the unneeded bits (rather than having to cast etc each time)

I have a field sID that is the primary key
0
 
Bill PrewCommented:
So there is an existing column that is varchar and has this text in it?  And you have a new column that is integer and you want to place the converted number in that?

Or are you wanting to update the existing varchar column just removing the decimal part of the "number"?

Or, if you are wanting something different please explain in more detail.


»bp
0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Hi Bill

I guess ideally just remove the decimal part from the current columns sEasting and sNorthing as they are currently varChar. I assume after that I could simply amend them to int

Regards
Neil
0
 
Bill PrewCommented:
I *think* this should work, give it a try in a test environment.  I believe SQL Server should take care of the type conversions...

UPDATE table1 SET sEasting = ROUND(sEasting,0,1), sNorthing = ROUND(sNorthing,0,1);


»bp
0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
perfect, many thanks
0
 
awking00Commented:
Just out of curiosity, could you try to alter those columns to int? If it completes, I suspect the data might be truncated as well so no update would be necessary.
For example, alter table table1 alter column (sEasting numeric 18,0).
0
 
Neil ThompsonSenior Systems DeveloperAuthor Commented:
Thanks awking00 and good point.

I must admit I never tried this as I initially tried to amend the column itself straight from varChar to int and it was failing on the decimal numbers but hopefully that may be useful to anyone looking at this issue in the futire
0
 
awking00Commented:
Were you trying to "amend" the column with an update or an alter table statement?
0

Join & Write a Comment

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now