How to convert nvarchar to varbinary accurately
database, sql, sql-server-2008
Solution
You're converting a string that happens to look like a binary value because it starts with `0x`. Unfortunately these are not the same thing, and in order for SQL Server to understand that you want to interpret the string as an actual binary value to convert directly, instead of a string to convert to its binary representation, you need to use a style parameter:
UPDATE dbo.tempuser -- please always use the schema prefix
SET [temp] = CONVERT(VARBINARY(MAX), [password], 1); -- and semi-colons
Note that converting an `NVARCHAR(MAX)` to `NVARCHAR(MAX)` is unnecessary.
Now, it is possible that because you chose `NVARCHAR(MAX)` for some reason, that the column contains garbage that can't be converted, so you may encounter this error:
Msg 8114, Level 16, State 5 Error converting data type nvarchar to varbinary.
In that case, you'll need to find the values that don't start with `0x` (or otherwise contain ineligible characters) and fix them.
Problem
I have a table which has column `[password]` stored as `nvarchar(max)`. I want to convert it into `varbinary(max)`. I created a new column called `[temp]` and declared it as `varbinary(max)`. Then I updated using `CONVERT`: ``` update tempuser set [temp]=CONVERT(varbinary(max), CONVERT(nvarchar(max),[password])) ``` Now in the [temp] column the value is different. For example, one value for `[password]` started with this: ``` 0x3E6AFF88... ``` The corresponding entry in [temp] starts with: ``` 0x30783345... ``` Also when `[password]` is `NULL`, `[temp]` becomes `0x4E554C4C`.