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
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
You can try doing an alter table. If it fails do this:
ALTER TABLE tableName ADD newCol int;
UPDATE tableName SET newCol = CAST(oldCol AS int)
;
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]
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
Can be done even simpler in just 2 steps
Update the column and set all non numberic values to null so alter won't fail.
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
Create a temp column
ALTER TABLE MYTABLE ADD MYNEWCOLUMN NUMBER(20,0) NULL;
Copy and casts the data from the old column to the new one
UPDATE MYTABLE SET MYNEWCOLUMN=CAST(MYOLDCOLUMN AS NUMBER(20,0));
Delete the old column
ALTER TABLE MYTABLE DROP COLUMN MYOLDCOLUMN;
Rename the new one to match the same name as the old one.
ALTER TABLE MYTABLE RENAME COLUMN MYNEWCOLUMN TO MYOLDCOLUMN;
Can you try this ?
alter table MyTable add MyColNum Varchar(500) null;
alter table MyTable add MyColNum int null;