CODEWITHSUNDEEP
sql-serversql-server-2008-r2
AndiDog
AndiDog

Reputation: 70208

Alter column from NVARCHAR to NBINARY

I accidentally created a column with the wrong type NVARCHAR (for storing password salts) and I want to convert it to NVARBINARY.

I tried

ALTER TABLE [dbo].[TableName]
    ALTER COLUMN [ColumnName] [varbinary] (20) NOT NULL
GO

but it says

Implicit conversion from data type nvarchar to varbinary is not allowed. Use the CONVERT function to run this query.

Is there a way to do that? CONVERT seems to be for expressions only, not for alterations.

Upvotes: 3

Views: 3893

Answers (2)

bummi
bummi

Reputation: 27385

The only way by altering will be someting like:

Create Table a (id int,blb Nvarchar(10))


insert into a Values
(1,'Test'),
(2,N'Test2');

BEGIN Transaction
ALTER TABLE a
ADD blb_New [varbinary] (20) NULL
GO

UPDATE a
SET blb_new = CAST(blb AS varbinary(20))
GO

ALTER TABLE a
DROP COLUMN blb
GO

EXEC sp_rename 'a.blb_new', 'blb', 'COLUMN'
GO
COMMIT Transaction


Select *,CAST(blb as Nvarchar(20)) from a

Drop Table a

Upvotes: 3

Abhishek
Abhishek

Reputation: 101

You may first convert all the values from NVARCHAR to NVARBINARY in the same column. After converting all the values use Alter table

You may refer the following link: Converting NVARCHAR(255) to DATE

Upvotes: 0

Related Questions