How to change datatype of a column containing data in sql server?

Question

I have a table in my SQL Server DB.

CREATE TABLE [dbo].[Table2]([col3] [varchar](50) NULL, [col4] [varchar](50) NULL, [col5] [char](1) NULL)

Following is the data in the table:

col3    col4    col5
sad     asdf    c
df      sdfg    b

Now I want to change the datatype of 'col5' to INT. Tried this:

alter table [dbo].[TABLE2] alter column [col5] int null;

Encountered this error:

Conversion failed when converting the varchar value 'c' to data type int.
The statement has been terminated.

After changing the datatype to INT - I want to change 'c' to 100 and 'b' to 101.

How can I change the datatype and the data? Please point me in the right direction.

gofr1 · Accepted Answer · 2016-03-30 13:10:24Z

2

First you must increase your col5:

alter table [dbo].[TABLE2] alter column [col5] varchar(10) null;

Then UPDATE your data to something such as numbers. Maybe:

UPDATE Table set col5 = '100' where col5='c'

gofr1

16k11 gold badges47 silver badges56 bronze badges

answered Jun 26, 2013 at 18:47

Jairon Alava

713 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

This does exactly what I was looking for. Thank you.

Devasayal · Accepted Answer · 2013-06-26 18:35:07Z

1

Alter column - wouldn't work for type conversions from varchar to int/long/etc.,

There could be smarter ways. However, this serves the purpose i guess.

answered Jun 26, 2013 at 18:35

Devasayal

408 bronze badges