Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL alter column datatype from nvarchar to int

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

like image 697
Mohit Jain Avatar asked Aug 28 '13 06:08

Mohit Jain


People also ask

Can we convert NVARCHAR to int in SQL?

SQL Server's CAST() and CONVERT() methods can be used to convert VARCHAR to INT.

Can we alter column type in SQL?

You can modify the data type of a column in SQL Server by using SQL Server Management Studio or Transact-SQL. Modifying the data type of a column that already contains data can result in the permanent loss of data when the existing data is converted to the new type.


4 Answers

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
like image 70
dcaswell Avatar answered Oct 10 '22 04:10

dcaswell


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]
like image 30
S. S. Rawat Avatar answered Oct 10 '22 05:10

S. S. Rawat


  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
like image 38
Alexandr Avatar answered Oct 10 '22 04:10

Alexandr


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

like image 44
Arne H. Bitubekk Avatar answered Oct 10 '22 04:10

Arne H. Bitubekk