19

Can the datatype of a field be changed to int from nvarchar??

alter table employee alter column designation int

is this valid?? If not can it be done in some other way??

P.S: I am using MS SQL Server

DineshDB
  • 5,998
  • 7
  • 33
  • 49
Mohit Jain
  • 733
  • 3
  • 9
  • 24
  • 1
    Yes I did. It gave an error saying cannot convert varchar to int. I am trying to figure out if there is some way through cast statememt. – Mohit Jain Aug 28 '13 at 07:22
  • 1
    That means that you have one or more *values* currently stored in that column that can't be converted to `int` - you need to clean up the data before you attempt the conversion. – Damien_The_Unbeliever Aug 28 '13 at 07:23

6 Answers6

23

You can try doing an alter table. If it fails do this:

  1. Create a new column that's an integer:

ALTER TABLE tableName ADD newCol int;

  1. Select the data from the old column into the new one:

UPDATE tableName SET newCol = CAST(oldCol AS int);

  1. Drop the old column
jophab
  • 5,356
  • 14
  • 41
  • 60
dcaswell
  • 3,137
  • 2
  • 26
  • 25
  • even selecting the data from old to new column will give problems because varchar wont be copied to an integer field. – Mohit Jain Aug 28 '13 at 07:23
  • This [here](http://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql?answertab=active#tab-top) is a way better answer and actually works. – Adam Klein Oct 27 '16 at 21:23
  • The @AdamKlein way could not be valid if you using SQL Server – Kyto Mar 17 '20 at 15:59
16

It is possible only when you column has no value or blank. If your column has some value which have nvarchar value and you should try to convert it into int, it will give error. 

ALTER TABLE [table_name] ALTER COLUMN [column_name] [data_type]
S. S. Rawat
  • 5,943
  • 4
  • 43
  • 59
  • Itwas working when column was blank. Is there no way through which varchar can be converted to int in sql? – Mohit Jain Aug 28 '13 at 07:24
  • Because every datatype have some limitation like size or value ie. int contain only numeric value and also 2 byte in size but nvarchar contain all type of value and their size caa be expandable – S. S. Rawat Aug 28 '13 at 07:28
  • 1
    The first statement in this answer is incorrect. `CREATE TABLE employee(designation NVARCHAR(10));INSERT INTO employee VALUES (N'10');alter table employee alter column designation int` works fine. – Martin Smith Aug 28 '13 at 07:40
  • This is incorrect - it's definitely possible to ALTER TABLE ALTER COLUMN if the datatypes actually in the table are CASTable to the new datatype, at least in some cases. – Dan Field Apr 15 '15 at 13:55
5
  1. Add new numeric column.
  2. Copy from old char column to new column with trim and conversion.
  3. Drop old char column.
  4. Rename numeric column to old column name.

This worked for me (with decimals but I suppose it will work with ints):

alter table MyTable add MyColNum decimal(15,2) null
go
update MyTable set MyColNum=CONVERT(decimal(15,2), REPLACE(LTRIM(RTRIM(MyOldCol)), ',', '.')) where ISNUMERIC(MyOldCol)=1
go
alter table MyTable drop column MyOldCol
go
EXEC sp_rename 'MyTable.MyColNum', 'MyOldCol', 'COLUMN'
go
Alexandr
  • 695
  • 2
  • 9
  • 18
4

Can be done even simpler in just 2 steps

  1. Update the column and set all non numberic values to null so alter won't fail.

  2. Alter the table and set the type to int.

UPDATE employee
SET designation = (CASE WHEN ISNUMERIC(designation)=1 THEN CAST(CAST(designation AS FLOAT) AS INT)END )

ALTER TABLE employee
ALTER COLUMN designation INT

This takes the assumption that that the columns allow nulls. If not then that needs to be handled as well. For example: By altering the column to allow null, then after it has been converted to int then set all null values to 0 and alter the table to not allow null

Arne H. Bitubekk
  • 2,963
  • 1
  • 27
  • 34
2

  1. Create a temp column

    ALTER TABLE MYTABLE ADD MYNEWCOLUMN NUMBER(20,0) NULL;

  2. Copy and casts the data from the old column to the new one

    UPDATE MYTABLE SET MYNEWCOLUMN=CAST(MYOLDCOLUMN AS NUMBER(20,0));

  3. Delete the old column

    ALTER TABLE MYTABLE DROP COLUMN MYOLDCOLUMN;

  4. Rename the new one to match the same name as the old one.

    ALTER TABLE MYTABLE RENAME COLUMN MYNEWCOLUMN TO MYOLDCOLUMN;

trungk18
  • 19,744
  • 8
  • 48
  • 83
Jorciney
  • 674
  • 10
  • 11
0

Can you try this ?

alter table MyTable add MyColNum Varchar(500) null;
alter table MyTable add MyColNum int null;
clemens
  • 16,716
  • 11
  • 50
  • 65
Suresh
  • 1