change varchar to decimal

rgb192
rgb192 used Ask the Experts™
on
change datatype of a column in a table
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ALTER TABLE MyTable ALTER COLUMN MyColumn DataType
AneeshDatabase Consultant
Top Expert 2009
Commented:
Hello rgb192,
thats not so easy if you have non numeric data on that column , in that case you may have to either drop that column or delete the non numeric values
Otherwise an alter table statement will do

Alter table tablename alter column columname decimal(10,2 )


Regards,

aneeshattingal
If its practical, you can always ALTER TABLE ADD COLUMN [numVarcharToDec] decimal(10,2)...
Then programatically populate the new column with the corresponding source column, ignoring values which cannot be converted. Then you can filter and decide what to do with those rows which couldn't be converted.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
In Sql Qry Analyser, go to Help->Transact Sql Help, in search type Alter Column, it has got good syntax and examples
check the below links it may help u

http://www.sqlmag.com/Article/ArticleID/44170/sql_server_44170.html

--Change to int from float
ALTER TABLE table1 ALTER COLUMN col1 int

--change from int to nvarchar(3)
ALTER TABLE table1 ALTER COLUMN col1 nvarchar(3)
ALTER TABLE <tablename>
Alter column <colname>l DECIMAL(18, 0) NOT NULL;

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial