Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gridview
Starting Member
11 Posts |
 Posted - 2008-12-29 : 09:46:35
|
| I have a field/column called A in table XX, initailly we decided to Varchar(50) as datatype for field/column A.Now we want to change the datatype of A to int.How can I do it? Also, field/column A does have data which is non-numeric.Please suggest.Thanks. |
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2008-12-29 : 09:54:21
|
| -- If the field has non numeric data then you will have to get rid of that data first.create table A ( xx varchar(25))insert into A values ('2')insert into A values ('A2S')insert into A values ('2A34567')select * from a --Remove non-numeric dataupdate A set xx = 0 where isnumeric(xx) = 0select * from Aalter table A alter column xx intdrop table A"God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-12-29 : 10:27:06
|
| the tough part (depending on your values) will be validation test for what can be converted to successfully to INT. jhocutt's solution of isnumeric() certainly won't work. It is not that reliable for several reasons but more obviously '1.2' is numeric but won't convert to INT without loosing precision.check out this topic (as well as search for other methods)http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59049&SearchTerms=isIntYou also need to decide what you want to do with values that can't convert directly to INT. round up/down non integer numeric data? change to NULL? delete entire row?Be One with the OptimizerTG |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
How to change datatype from varchar to int